Help with code needed

[Les Stout]

Hi all, i was kindly helped with some code to compare and past by Tom
Ogilvy and wouold like to know if it could be used to to compare the two
ranges and delete entire line, if so how would i change it, i have tried
but cannot do it.

Sub ABC()
Dim sh1 as worksheet, sh2 as worksheet, sh3 as worksheet
Dim rng1 as Range, rng2 as Range
Dim rw as Long, cell as Range
set sh1 = worksheets("19_10_06")
set sh2 = worksheets("Oct_06")
set sh3 = worksheets("gAMS")
rw = 2
set rng1 = sh1.Range(sh1.Cells(2,1),sh1.Cells(2,1).End(xldown))
set rng2 = sh2.Range(sh2.Cells(2,1),sh2.Cells(2,1).End(xldown))
for each cell in rng1
if application.countif(rng2,cell.value) = 0 then
cell.EntireRow.copy sh3.cells(rw,1)
rw = rw + 1
end if
Next

End sub

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[Les Stout]

I have tried the following with no success:-
Sorry, i am not a programmer....

Sub ABC()
Dim sh1 as worksheet, sh2 as worksheet, sh3 as worksheet
Dim rng1 as Range, rng2 as Range
Dim rw as Long, cell as Range
set sh1 = worksheets("Tabelle1")'**Changed
set sh2 = worksheets("Temp")'**Changed
'** Changed
rw = 2
set rng1 = sh1.Range(sh1.Cells(2,1),sh1.Cells(2,1).End(xldown))
set rng2 = sh2.Range(sh2.Cells(2,1),sh2.Cells(2,1).End(xldown))
for each cell in rng1
if application.countif(rng2,cell.value) = 0 then
cell.EntireRow.Delete sh2.Cells(rw, 1)'**Changed
rw = rw + 1
end if
Next

End sub


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

 

[NickH]

Les,

Make sure you have a backup copy before you run it but if sh2.Cells(rw,
1) is in the row you want to delete then you need to change the line...

cell.EntireRow.Delete sh2.Cells(rw, 1)

to

sh2.Cells(rw, 1).EntireRow.Delete

HTH NickH

 

[Les Stout]

Thanks NickH, is that all i change ??

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***