Trying to delete rows with cells that have same value as previous in VBA

Question

I am trying remove duplicate records using the A column, and I seem to have some success, but I have still found duplicates. Hoping someone will tell me what I can do to improve this.

Thanks

Dim i       As Integer
Dim value   As String

i = 3
Range("A" & i).Select

Do Until IsEmpty(ActiveCell)
    Range("A" & i).Select
    value = ActiveCell.value
    Do Until IsEmpty(ActiveCell)
        If ActiveCell.value = value Then
            ActiveCell.EntireRow.Delete
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop
    i = i + 1
Loop

Do you want only the consecutive duplicates to be removed from column A? — Gowtham Shiva, Aug 18 '17 at 16:04
FYI - It's best practice to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Aug 18 '17 at 16:31

score 4 · Answer 1 · answered Aug 18 '17 at 16:09

Avoid using select and active cell.

Use a For loop that loops backwards.

Dim i As Long
Dim ws As Worksheet

Set ws = ActiveSheet 'This can be changed to a specific sheet: Worksheets("sheetName")

With ws

    For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 3 Step -1
        If .Cells(i, 1).value = .Cells(i - 1, 1).value Then
            .Rows(i).Delete
        End If
    Next i
End With

This will only compare the value to the one above. If you want to remove all duplicates use @Petay87 answer.

suggestion - when doing this set the rows you want to delete to a Range variable then delete the Range at the end. Much faster when doing this on larger data sets — Tom, Aug 18 '17 at 16:13

score 3 · Accepted Answer · answered Aug 18 '17 at 16:00

3

I believe the following will work for you if you are removing the duplicates for all of column A and it does not have a header row:

ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo

answered Aug 18 '17 at 16:00

Petay87

1,700
5
24
39

It looks like the asker is only looking to delete consecutive duplicate values. – davidmneedham Aug 18 '17 at 16:03
His code is to remove only consecutive dupes i guess. – Gowtham Shiva Aug 18 '17 at 16:05
Because the OP wants the whole row removed, change `ActiveSheet.Range("A:A")` to `ActiveSheet.Range("3:10408576")` – Scott Craner Aug 18 '17 at 16:11
Thanks, I did not know there was already a method that did what I needed (still new to VBA). As I read more on it I saw that there was a value for columns ; Columns:=Array(1,2) I understand that the 2 looks for duplicates in the first 2 columns but what is the 1 for? – Jaymes Aug 18 '17 at 16:31
@Jaymes in the array, each column is represented on its on; `Array(2,3)` would look in the second and third columns for the duplicates. So `Array(1,2,5)` looks in the first, second and fifth column. – Scott Craner Aug 18 '17 at 16:33

score 1 · Answer 3 · answered Aug 18 '17 at 16:12

1

When you delete a row, you need to loop through in reverse. Try the below code,

Sub removeDupes() 
Dim i As Long 
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row 
    If Range("A" & i).Value = Range("A" & i + 1).Value Then 
        Range("A" & i + 1).ClearContents 
    End If 
Next i 
For i = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1 
    If IsEmpty(Range("A" & i)) Then 
        Range("A" & i).EntireRow.Delete 
    End If 
Next i 
End Sub

answered Aug 18 '17 at 16:12

Gowtham Shiva

3,802
2
11
27

1

Why two steps? There is no reason to just delete. – Scott Craner Aug 18 '17 at 16:14
@ScottCraner Just for the OP to understand the functionality better – Gowtham Shiva Aug 18 '17 at 16:20

Trying to delete rows with cells that have same value as previous in VBA

3 Answers3

Linked