I am looking to build or find a macro that will delete rows from one sheet dependent on the value of a selected cell in another sheet.
(Excel 2003)
i.e.: When on a cell (say A1) in sheet 1 and the macro is ran it will find any row in sheet 2 with the same value (in A column on sheet 2), and delete the row(s).
I hope this explains it well enough.
Thanks
Loren
Last edited by MisterrMr; 05-11-2010 at 02:10 PM.
Hello MisterrMr,
Welcome to the Forum!
This macro allows you to delete rows on any worksheet in the workbook . It uses the search value you supply. This can be from a cell or an expression you supply to the macro. This can not be used as a worksheet formula (UDF). It can only be run in VBA.
Please Login or Register to view this content.
[b]Example of Using the Macro[/code]
[/code]
Sub TestDeleteRows()
CustomDeleteRows Worksheets("Sheet1").Range("A1"), Worksheets("Sheet2").Range("A1"), False
End Sub
[/code]
Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Your initial Macro example worked like a champ. The second sheet was sorted on the first column (A) and then the first row (1) was deleted because the value in Sheet1.A1 matched Sheet2.A1
Since my objective is to use the value of a selected cell in Sheet1 to delete rows from sheet 2 that have that value in a specified column, I tried altering your example to use ActiveCell.Address as the first parameter. This does nothing. It doesn't even sort the second sheet. Not even if I select A1 This tells me, the reference passed is not producing the value I want even though it does return the currently selected cell.
As a further test I simply changed the A1 reference for the first sheet to A2. Again, nothing.
I'm totally confused now. I must not be understanding something.
I should also note: I will be needing this to run in 2007 not 2003 as I first though. So I have been working on this at home tonight with 2007.
I really hope that's the problem.
Last edited by MisterrMr; 05-12-2010 at 01:34 AM.
OKAY! I found the problem!
As you can see in the spreadsheet I attached above, all the values in the first column are numbers. But, if I change them to strings, it comes closer to working.
The line
appears to not work right with the value being a number causingPlease Login or Register to view this content.
to fail. (2003 vs. 2007 difference?)Please Login or Register to view this content.
The next issue is that only the first column is being sorted which puts the sheet all out of wack.
Hello MisterrMr,
Your right that the variable LookFor should be a string. I made that change in the code below. There are differences in sorting syntax between 2003 and 2007. I do know that converting from 2007 back to 2003 doesn't always work with the sorting code. The same may hold true going the other way. Not having 2007, I can not be sure. Test it out and let me know.
Please Login or Register to view this content.
I've can't test in 2007 until tonight but in 2003 it is still only sorting the first column, leaving the rest of the data where it is.
I think I could manage to alter your code to just test each cell and delete on a match but I know that's going to increase run time for long ranges so I would like to keep the sorting if we can get it to sort properly.
Hello MisterrMr,
How do you want it to be sorted?
The entire row needs to sort with the first column as the criteria.
if the table has.
a-b-c-d-e
1-2-3-4-5
2-3-4-5-6
3-4-5-6-7
4-5-6-7-8
What happens now is the first column is sorted, bringing the cell with correct data to the top and that row is deleted. For example, if the the match value is 3 the table looks like this after the function.
1-2-3-4-5
2-3-4-5-6
4-4-5-6-7
a-5-6-7-8
when it should look like this after:
1-2-3-4-5
2-3-4-5-6
4-5-6-7-8
a-b-c-d-e
I just found out, the delete is starting with the top row even if header row is set True: the table looks like this
3-4-5-6-7
1-2-3-4-5
2-3-4-5-6
4-5-6-7-8
when it should be
a-b-c-d-e
1-2-3-4-5
2-3-4-5-6
4-5-6-7-8
I hope that's not too much - I can put the examples into a spreadsheet going forward if needed.
Hello MisterrMr,
Not too much at all, it tells me everything I need to know. I work on correcting these issues. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks