Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare two worksheets and delete rows
I currently have this existing code that checks to if the value is the
same between col E and P and if true, the row is deleted. 'lastrow = Cells(Rows.Count, 4).End(xlUp).Row 'For i = lastrow To 1 Step -1 ' If Cells(i, "E").Value = Cells(i, "P").Value Then ' Rows(i).Delete 'End If 'Next How can I change this to compare values between col A in worksheet 1 and col A in worksheet 2, and if the value is the same, delete that row in worksheet 1? Thank you, Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare two worksheets and delete rows
Dim sh1 as Worksheet, sh2 as Worksheet
Dim i as Long set sh2 = Worksheets(2) set sh1 = Worksheets(1) lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row For i = lastrow To 1 Step -1 If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value Then sh1.Rows(i).Delete End If Next you can replace the 2 and 1 (refers to tab order) with actual worksheet names such as Sheet3 and Sheet4 for example. set sh1 = Worksheets("Sheet3") -- Regards, Tom Ogilvy " wrote: I currently have this existing code that checks to if the value is the same between col E and P and if true, the row is deleted. 'lastrow = Cells(Rows.Count, 4).End(xlUp).Row 'For i = lastrow To 1 Step -1 ' If Cells(i, "E").Value = Cells(i, "P").Value Then ' Rows(i).Delete 'End If 'Next How can I change this to compare values between col A in worksheet 1 and col A in worksheet 2, and if the value is the same, delete that row in worksheet 1? Thank you, Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare two worksheets and delete rows
Thanks Tom, you've always been great on helping out!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare two worksheets and delete rows
Quick questions...the macro runs fine with some small test data. I ran
the macro where sh2 had 547 rows and sh1 had 2800 rows. There was a dupe on sh1 and sh2. On row 2820 sh1 and on row 547 sh2. However the macro did not remove the row in sh1. Any ideas why? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare two worksheets and delete rows
Not to usurp any credit from Tom, nor to "show him up", but this ma work. Replace If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value Then with If sh1.Cells(i, "A").Value = Cells.Find(sh1.Cells(i, "A").Value ActiveCell, xlFormulas, xlWhole, xlByColumns, xlPrevious, False, False Then This should search the entire sheet2 to find the value on sheet -- bgeie ----------------------------------------------------------------------- bgeier's Profile: http://www.excelforum.com/member.php...fo&userid=1282 View this thread: http://www.excelforum.com/showthread.php?threadid=54599 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare two worksheets and delete rows
Mike, I modelled the code on what you had provided - it only checks for rows
in the same location. It is easily altered however to check for a match anywhere in column 1 of Sheet2. Dim sh1 as Worksheet, sh2 as Worksheet Dim i as Long, rng as Range Dim lastRow as Long set sh2 = Worksheets(2) set sh1 = Worksheets(1) lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row With sh2 set rng2 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup)) End With For i = lastrow To 1 Step -1 If Application.Countif(rng2,Sh1.Cells(i,"A").Value)0 then sh1.Rows(i).Delete End If Next -- Regards, Tom Ogilvy wrote in message oups.com... Quick questions...the macro runs fine with some small test data. I ran the macro where sh2 had 547 rows and sh1 had 2800 rows. There was a dupe on sh1 and sh2. On row 2820 sh1 and on row 547 sh2. However the macro did not remove the row in sh1. Any ideas why? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare two worksheets and delete rows
bgeier,
Not to usurp any credit from Tom, nor to "show him up", but this may work. Don't give it a second thought. We are all working to help the user and any meaningful contribution is most welcome. In that same vein, here is an implementation of my code using your method that actually does work. I restricted it to look in only column A of the second sheet based on what the OP originally posted. Sub AAEE() Dim sh1 As Worksheet, sh2 As Worksheet Dim i As Long Set sh2 = Worksheets(2) Set sh1 = Worksheets(1) lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row For i = lastrow To 1 Step -1 Set rng = sh2.Columns(1).Find( _ sh1.Cells(i, "A").Value, sh2.Cells(Rows.Count, 1), _ xlFormulas, xlWhole, xlByColumns, _ xlPrevious, False) If Not rng Is Nothing Then sh1.Rows(i).Delete End If Next End Sub -- Regards, Tom Ogilvy "bgeier" wrote in message ... Not to usurp any credit from Tom, nor to "show him up", but this may work. Replace If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value Then with If sh1.Cells(i, "A").Value = Cells.Find(sh1.Cells(i, "A").Value, ActiveCell, xlFormulas, xlWhole, xlByColumns, xlPrevious, False, False) Then This should search the entire sheet2 to find the value on sheet1 -- bgeier ------------------------------------------------------------------------ bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822 View this thread: http://www.excelforum.com/showthread...hreadid=545996 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Rows and delete duplicate records | Excel Worksheet Functions | |||
Help! -How to compare two worksheets; multiple rows of data=result | Excel Worksheet Functions | |||
Compare 1st 8 digits of rows in three worksheets | Excel Worksheet Functions | |||
Add or Delete Rows in Protected worksheets | Excel Discussion (Misc queries) | |||
Using VBA to delete certain worksheets rows | Excel Programming |