Delete Entire row if value don’t match

  • Thread starter Thread starter K
  • Start date Start date
K

K

Hi all, I got list in Range("A2:A10") of sheet 1 like see below

A………col
LC1
X2Y
GFS
TLL
FST
NWM
ZRS
TWW
UIS

I want macro to check in column A of sheet 2 the values of Range
("A2:A10") of sheet 1 and if any value in column A of sheet 2 don’t
match with the values of sheet 1 then macro should delete entire row
in sheet 2
Please can any friend can help
 
with Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).end(xlup).Row
RowCount = LastRow
Do while RowCount >= 1
Data = .Range("A" & Rowcount)
set c = Sheets("Sheet1").Columns("A").find(what:=Data, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
c.entirerow.delete
end if
RowCount = RowCount - 1
loop
end with
 
Hi Joel

I tried your code on a test Sheet1 & Sheet2.

It loops through ok but stops on the line c.EntireRow.Delete when it finds a
value not on both sheets.

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.
 
Back
Top