Delete Entire row if value don’t match
 

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

Delete Entire row if value dont match
 

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

"K" wrote:

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 dont
match with the values of sheet 1 then macro should delete entire row
in sheet 2
Please can any friend can help
.

Delete Entire row if value dont match
 

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.


"Joel" wrote:

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

"K" wrote:

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 dont
match with the values of sheet 1 then macro should delete entire row
in sheet 2
Please can any friend can help
.

Delete Entire row if value dont match
 

I was deleting from the wrong sheet.

from
c.entirerow.delete
to
..Rows(RowCount).Delete



"Aussie Bob C" wrote:

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.


"Joel" wrote:

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

"K" wrote:

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 dont
match with the values of sheet 1 then macro should delete entire row
in sheet 2
Please can any friend can help
.