Delete all duplicate rows Excel vba doesn´t work correctly

Question

I´ve created a workbook in excel with a userform which allows you to import .csv data in a worksheet. Know I´ve the problem with duplicate rows. I found a solution here, but it is not working 100%. (Delete all duplicate rows Excel vba) The script delete most of the duplicate data, but there are still 2-5 duplicate rows. First I thought it´s the format of the cells, but thats not the problem...

Here is the makro:

    Sub DeleteRows()
       With ActiveSheet
           Set Rng = Range("A1", Range("T1").End(xlDown))
            Rng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20), Header:=xlYes
       End With
    End Sub

The duplicate data which is left over start with a negativ number. I don´t know if this will help to find a solution ...

Can you provide a sample of duplicate rows that are not deleting? The only time I'm seeing issues are when there are blank cells in the rows. — tittaenälg, Jan 07 '15 at 13:01

score 1 · Accepted Answer · answered Jan 12 '15 at 12:40

I don´t know the real issue but i fixed it by adding a dot in each cell. Exanmple:

1. Cell1  -> .Cell1
2. Remove the duplicates
3. .Cell1 -> Cell1

Here is the code:

Sub DeleteRows()
Dim c As Long
With ActiveSheet
    For c = 1 To 20
        If Application.CountA(.Columns(c)) Then
            .Columns(c).TextToColumns Destination:=.Cells(1, c), _
              DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
        End If
    Next c
    With .Range("A1", .Range("T1").End(xlDown)).Cells
        .RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20), Header:=xlYes
    End With
End With
End Sub

Private Sub CommandButton2_Click()
.
.'some code'
.
For i = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
    column = ActiveSheet.Range("A" & i).value
    If column < 0 Then
        column = "." & column
        ActiveSheet.Range("A" & i).value = column
    End If
Next

Call DeleteRows

For i = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
    firstChar = Left(ActiveSheet.Range("A" & i).value, 1)
    If firstChar = "." Then
        column = Right(ActiveSheet.Range("A" & i).value, Len(ActiveSheet.Range("A" & i).value) - 1)
        ActiveSheet.Range("A" & i).value = column
    End If
Next
.
.'some code'
.
End Sub

It´s not the best solution but it works! Thx for the help.

score 0 · Answer 2 · answered Jan 07 '15 at 12:53

I strongly suspect that the negative sign is not a conventional CHAR(45) hyphen or has leading/trailing zeroes. Are some of the values left aligned while other are right aligned? This would indicate that some are considered text and others true numbers.

The quickest way to remove leading/trailing spaces in a Text-to-Columns ► Fixed Width on each column.

Sub DeleteRows()
    Dim c As Long
    With ActiveSheet
        For c = 1 To 20
            If Application.CountA(.Columns(c)) Then
                .Columns(c).TextToColumns Destination:=.Cells(1, c), _
                  DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
            End If
        Next c
        With .Range("A1", .Range("T1").End(xlDown)).Cells
            .RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20), Header:=xlYes
        End With
    End With
End Sub

There may be less obvious reasons for this; non-breaking spaces (ASCII 160 or 0×A0) comes immediately to mind. In lieu of actually showing some sample data, you need to examine your values closely to see what is causing Excel not to treat the values as duplicates.

First of all thanks for your quick answer! But the same lines are still there :/ — Metabinary, Jan 07 '15 at 13:00
@Cryxon -Pick two values that look like they are duplicates; say A5 and A9. In an unused row, use this formula =CODE(MID($A$5, COLUMN(A:A), 1)) and underneath it use the same formula replacing $A$5 with $A$9. Fill right. Are the numbers generated exactly the same? — , Jan 07 '15 at 13:13
I picked 2 rows that are exact the same and checked it with the formular. I changed the commas in semicolons and the outpot was "#NAME?". So I checked the values of the rows with the formular =CODE() and the values were exact the same. — Metabinary, Jan 08 '15 at 07:00
Upload a sample of the 'duplicate' data in a redacted worksheet to a public file repository and post a *publically shared* link to the worksheet back here in your original question. — , Jan 08 '15 at 07:05

Delete all duplicate rows Excel vba doesn´t work correctly

2 Answers2