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

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

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