CODEWITHSUNDEEP
vbaexcel
Tristan Parrish
Tristan Parrish

Reputation: 43

Deleting entries in excel macro when entries are different

I am currently trying to create a program that will Search through an excel file and delete duplicate entries.

I have made this code that does this. However, I also need it to delete the entry before the duplicate and after the duplicate. I've looked everywhere and can not find any examples, please help!

These are my example entries

The1
Car
Car
The2

I'd need it to delete The1 and both Car entries leaving the The2. Here is my code so far

Sub rar()
    Dim i As Long
    With Worksheets("Sheet1") 'DEFINES WHICH SHEET TO USE'
      For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
          Rows(i+1).Delete
          Rows(i).Delete
          Rows(i-1).delete  
        End If
     Next i
  End With   
End Sub

Upvotes: 2

Views: 35

Answers (1)

CallumDA
CallumDA

Reputation: 12113

As Chris Neilsen pointed out below - using With was a great idea, but you need to put a . before your cell and range references to ensure they refer to the worksheet you specified in your With token

Try this:

Sub rar()
    Dim i As Long, rng As Range
    With Worksheets("Sheet1") 'DEFINES WHICH SHEET TO USE'
      For i = 3 to .Cells(.Rows.Count, "A").End(xlUp).Row
        If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then
            If rng Is Nothing Then
                Set rng = .Rows(i - 2 & ":" & i)
            Else
                Set rng = Union(rng, .Rows(i - 2 & ":" & i))
            End If 
        End If
     Next i
  End With 
  rng.Delete  
End Sub

Note: untested - but the idea is to create build up your range as you loop through and then delete at the end. This way you don't have to worry about looping backwards.

Upvotes: 2

Related Questions