Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling all MVPs! Macro to delete non matches b/t two lists. Thank
I need a macro that deletes rows in tab "A" if the IDs in column A do not
match those in column "A" of the master list on tab "B". So, if the IDs match, then they're kept; non matches are deleted. I tried to modify a delete duplicates macro I use regularly, but have had no luck. Thanks for your help. Excel User |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling all MVPs! Macro to delete non matches b/t two lists. Thank
Sub DeleteRows()
Dim rng as Range, i as Long Dim lastrow as Long With worksheets("B") set rng = .Range(.cells(1,1),.cells(rows.count,1).End(xlup)) End with with worksheets("A") lastrow = .Cells(rows.count,1).End(xlup).Row for i = lastrow to 1 step -1 if application.Countif(rng,.cells(i,1)) = 0 then .rows(i).Delete end if Next i End with End Sub Adjust to match your situation Test on a copy of your data -- Regards, Tom Ogilvy "Excel User" wrote in message ... I need a macro that deletes rows in tab "A" if the IDs in column A do not match those in column "A" of the master list on tab "B". So, if the IDs match, then they're kept; non matches are deleted. I tried to modify a delete duplicates macro I use regularly, but have had no luck. Thanks for your help. Excel User |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling all MVPs! Macro to delete non matches b/t two lists. T
this works! thanks for your valuable guidance!
EU "Tom Ogilvy" wrote: Sub DeleteRows() Dim rng as Range, i as Long Dim lastrow as Long With worksheets("B") set rng = .Range(.cells(1,1),.cells(rows.count,1).End(xlup)) End with with worksheets("A") lastrow = .Cells(rows.count,1).End(xlup).Row for i = lastrow to 1 step -1 if application.Countif(rng,.cells(i,1)) = 0 then .rows(i).Delete end if Next i End with End Sub Adjust to match your situation Test on a copy of your data -- Regards, Tom Ogilvy "Excel User" wrote in message ... I need a macro that deletes rows in tab "A" if the IDs in column A do not match those in column "A" of the master list on tab "B". So, if the IDs match, then they're kept; non matches are deleted. I tried to modify a delete duplicates macro I use regularly, but have had no luck. Thanks for your help. Excel User |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I delete all matches in a workbook? | Excel Worksheet Functions | |||
Compare lists and highlight matches | Excel Discussion (Misc queries) | |||
Compare lists and highlight matches | Excel Discussion (Misc queries) | |||
An Algorithm that matches two lists | Excel Programming | |||
An Algorithm that matches two lists | Excel Programming |