Delete Rows from sheet 1 from values in sheet 2
 

Hi,

Sheet 2 has the values

A B
1 CAR BUS
2
3

Sheet 1 Has

A B
1 BUS
2 TRAIN
3 CAR
4 BIKE
5 BUS

I want to programatically select as a range CAR BUS (sheet 2) and delete
other entire rows in sheet 2

i.e

A B
1 BUS
2 CAR
3 BUS

Thanks for all future help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200911/1

Delete Rows from sheet 1 from values in sheet 2
 

dim source as range
dim basedata as string
set source = worksheets("sheet2").Range("A1")
do until source = ""
basedata = basedata & "," & source.Value
set source = sourcde.Offset( ,1)
loop
set source = worksheets("sheet1").Range("B1").End(xlDown)
do
if Instr(basedata,source) =0 then
worksheets("sheet1").Rows(source.row).Delete
end if
if source.row=1 then exit do
set source = source.offset(-1)
loop

' basedata will be a concatenated string of your sheet2 row..."CAR,BUS"
the second loop reads each item and checks if its in the list, eg
instr("CAR,BUS","BUS") will = 5 while
instr("CAR,BUS","DOG") will be zero, and that row deleted




"FIRSTROUNDKO via OfficeKB.com" <u15639@uwe wrote in message
news:9fb26cce03583@uwe...
Hi,

Sheet 2 has the values

A B
1 CAR BUS
2
3

Sheet 1 Has

A B
1 BUS
2 TRAIN
3 CAR
4 BIKE
5 BUS

I want to programatically select as a range CAR BUS (sheet 2) and delete
other entire rows in sheet 2

i.e

A B
1 BUS
2 CAR
3 BUS

Thanks for all future help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200911/1