Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code needed
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code needed
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code needed
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code needed
Thanks NickH, is that all i change ??
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code needed | New Users to Excel | |||
Code Needed | New Users to Excel | |||
VBA code help needed | Excel Discussion (Misc queries) | |||
Code Fix Needed | Excel Programming | |||
code needed | Excel Programming |