Delete row if Col A & B are same

jwasten

Board Regular
Joined
May 29, 2002
Messages
90
If a row contains the same data in Col A and B as the row just below it, I want to keep only the top row. In other words, if there are 10 rows that all have the same data in Col A and Col B, I want to keep only Row 1.

A macro I have used successfully on another spreadsheet won't work on this one and I don't know why! That macro is as follows -
Dim r As Long
Dim c As Long
Dim lastRow As Long
Dim lastCol As Long
Dim dup As Boolean
Dim firstRow As Long
Dim firstCol As Long
With ActiveSheet.UsedRange
firstRow = .Cells(1).Row + 1
lastRow = .Cells(.Cells.Count).Row
firstCol = .Cells(1).Column
lastCol = .Cells(.Cells.Count).Column
End With
For r = lastRow To firstRow Step -1
dup = True
For c = firstCol To lastCol
If Cells(r, c).Value <> Cells(r - 1, c).Value Then
dup = False
Exit For
End If
Next
If dup Then Rows(r).Delete
Next

Beep
End Sub

When I run this macro, it "flutters" and looks like it is working and beeps when it's finished - but has actually deleted nothing! I can't figure out why. Please help!!!

Thanks!

Janet
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
I do this. In another column to the right I add this formula:
=A1=B1
Then I copy this all the way down my list.
Then I sort by true or false.
I then delete all the rows with true in them.
If you need it as a macro, maybe someone else can help you.

Michael
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Actually datsmart gave me this code in the past:
:)

Code:
Sub DeleteDupeRows() 
    Dim Rng1 As Range 
    Dim Rng2 As Range 
    Dim a As Range 
    Dim b As Range 
    
    Set Rng1 = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A65536").End(xlUp).Row) 
    Set Rng2 = Sheets("Sheet2").Range("B1:B" & Sheets("Sheet2").Range("C65536").End(xlUp).Row) 
    
    For Each a In Rng1 
        For Each b In Rng2 
            If b.Value = a.Value Then 
                b.EntireRow.Delete 
            End If 
        Next b 
    Next a 
End Sub

Hope this helps!
Michael :p
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Try this:
Code:
Sub RemoveEm()
Dim Limit As Long
Dim c As Long
Limit = Cells(Rows.Count, 1).End(xlUp).Row
For c = Limit To 2 Step -1
    If Cells(c, 1) = Cells(c - 1, 1) And Cells(c, 2) = Cells(c - 1, 2) Then
        Rows(c).Delete shift:=xlUp
    End If
Next c
End Sub
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739

ADVERTISEMENT

Daniels, I am not sure that is what he was going for, but I might be misunderstanding the post. I am thinking the OP wishes to delete if Column A and B are the same of another row....

Code:
Option Explicit
Sub Test()
Dim lastrow As Long, firstrow As Long, x As Long, r As Long, Ce As Range, Ce2 As Range

Application.ScreenUpdating = False
With ActiveSheet.UsedRange
    firstrow = .Cells(1).Row + 1
    lastrow = .Cells(.Cells.Count).Row
End With

For r = lastrow To firstrow Step -1
    Set Ce = Cells(r, 1)
    For x = r - 1 To firstrow Step -1
        Set Ce2 = Cells(x, 1)
        If Ce2.Value = Ce.Value And Ce2.Offset(0, 1).Value = Ce.Offset(0, 1).Value Then
            Ce.EntireRow.Delete
            Exit For
        End If
    Next x
Next r
Application.ScreenUpdating = True

End Sub
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
:oops:
Ahhh! Brian
After re-reading the original post, i think you may be right!!
How embarrassing :oops: :oops: :oops:
Michael
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Now, I have to back up a bit.. I think Lewiy got this one right on the nose... the op just wanted to check the row below not all the rows below. I took it a step too far. Good job Lewiy.
 

jwasten

Board Regular
Joined
May 29, 2002
Messages
90
THANKS!!!

I tried the code from Lewly since it was first and shortest!! And it worked GREAT!!

Thank you - thank you to everyone.

Janet
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,100
Messages
5,768,095
Members
425,452
Latest member
htay44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top