CODEWITHSUNDEEP
vbaexcel
user3776607
user3776607

Reputation: 5

Excel VBA: create macro that deletes a row that has a cell of equal value in two columns

I normally code C but I'm being forced to use VBA and I've been tearing my hairout all day.

Scenario: Every day the engineer I work for gets hundreds of documents, he has a macro that unzips and lists the documents in an excel sheet. Column A is UniqueID, B is Zip File Name, C is File Name. Each Zip file has a cover letter which will have the same name as Zip File Name.This cover letter is useless and it needs to be deleted.

I need a macro that goes through and deletes an entire row when the row holds coverletter info. For example, if B4=C4 delete row 4 or if B5=C5, delete row 5 etc. Everything I try has returned error. I have come up with this :

Sub DeleteDuplicateEntries()
    Dim Cell As Range, Cel As Range, N&
    Application.ScreenUpdating = False
    N = 0
    For Each Cell In Selection
         ' ignore empty cells
        If Cell <> Empty Then
            For Each Cel In Selection
                 'compare non-empty cel values
                 'and clear contents if duplicated value
                If Cel <> Empty And _
                Cel.Value = Cell.Value And _
                Cel.Address <> Cell.Address Then
                    Cel.ClearContents
                    N = N + 1
                End If
            Next Cel
        End If
    Next
    Application.ScreenUpdating = True
   End Sub

PROBLEM: It ends up delting every Zip File Name (other than the first one) but it doesn't delete the row :(

Upvotes: 0

Views: 2030

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96771

Try this small macro:

Sub rowKiller()
    Dim N As Long, i As Long
    N = Cells(Rows.Count, "B").End(xlUp).Row
    For i = N To 2 Step -1
        If Cells(i, "B").Value = Cells(i, "C").Value Then
            Cells(i, "B").EntireRow.Delete
        End If
    Next i
End Sub

Upvotes: 1

Related Questions