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

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

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

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