Thread: How to remove both duplicate and corresponding cells

Originally Posted by pike

hi Adam845
welcome to excel forum, so how do we know which ones have your product and need to be deleted ?

Hi pike,

The customers that own the product are in the rows with just the email address. So, I need to figure out how remove both that row and the row above with their first name, last name, and email address, ultimately resulting in a list of customers that don't own our product yet. I've been deleting both rows manually for months (extremely time consuming), but I'm sure there is an easier way to do it. Thanks for the response; I hope that clarifies my issue.

Originally Posted by pike

aaarh that will be

Sub ptest()
    Dim xRow   As Long
    Application.ScreenUpdating = False
    For xRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If IsEmpty(Cells(xRow, "A")) And Cells(xRow, "C") = Cells(xRow - 1, "C") Then
            Cells(xRow, "A").EntireRow.Delete xlShiftUp
            Cells(xRow, "A").EntireRow.Delete xlShiftUp
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Thanks for this, pike.

Unfortunately the code doesn't seem to be working on my end, though I could be entering it incorrectly. These are the steps I took:

Copy the Excel VBA code that you want to use
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

After running the macro it deleted some of the rows with email addresses, but I was still left with some customers that had the product, but only the row with their email address (e.g. from my example "Randall Turner") and it deleted others that I still needed to contact (e.g. from my example "John Carpenter").

Originally Posted by pike

so only the active sheet blank cell row?

Sub ptest()
    Dim xRow   As Long
    Application.ScreenUpdating = False
    For xRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If IsEmpty(Cells(xRow, "A")) And Cells(xRow, "C") = Cells(xRow - 1, "C") Then
            Cells(xRow, "A").EntireRow.Delete xlShiftUp
           End If
    Next
    Application.ScreenUpdating = True
End Sub

Close... Thanks! But that just removes the row with the email addresses. I've attached a spreadsheet with the desired result; hopefully that helps.

Originally Posted by pike

LOL.. left out a -1

Just couldnt see it was missing

Sub ptest()
    Dim xRow   As Long
    Application.ScreenUpdating = False
    For xRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If IsEmpty(Cells(xRow, "A")) And Cells(xRow, "C") = Cells(xRow - 1, "C") Then
            Cells(xRow, "A").EntireRow.Delete xlShiftUp
            Cells(xRow - 1, "A").EntireRow.Delete xlShiftUp
         End If
    Next
    Application.ScreenUpdating = True
End Sub

Awesome! Thank you so much. You just made my day!