Delete row if Col A & B are same

[jwasten]

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

 

[daniels012]

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]

Actually datsmart gave me this code in the past:

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

 

[Lewiy]

Try this:

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]

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....

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]

Ahhh! Brian
After re-reading the original post, i think you may be right!!
How embarrassing
Michael

 

[brian.wethington]

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]

THANKS!!!

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

Thank you - thank you to everyone.

Janet