VBA to delete duplicates

[VbaHell]

Hello all

I am trying to delete all the duplicate rows base on the values in column "A"

The rows are dynamic as in the number of rows and the code below will not work

If I manually select the range and use Data - Remove duplicates it works fine but if I record this it will not work

Any ideas please

 Range("A1:Z50000").Select
Range("A1:Z50000").RemoveDuplicates Columns:=Array(1), Header:=xlNo

 

[JackDanIce]

Try:

Dim x as Long
x = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:Z" & x).RemoveDuplicates Columns:=1, Header:=xlNo

 

[VbaHell]

Thanks JackDanIce

Just found this as well that seems to work but I will use your code

Sheets("Comments").Select
   Range("A1").Select
   ActiveSheet.Range("A:Z").RemoveDuplicates Columns:=1, Header:=xlNo

 

[JackDanIce]

You don't need to use .Select, this is just an action or attribute of Range and usually slows your code exeuction down when used.

That code is removing duplicates across all columns from A:Z, i.e. 1,048,576 rows x 26 columns (27,262,976 cells) to test for duplicates in column A and remove.

Suggested code, where x contains the row number of the last found row in column A means it's testing x rows x 26 columns for duplicates in column A to remove. Any time x is less than 1,048,576 will mean fewer cells to test and/or remove.

Is your data filling in all the rows in the sheet?

 

[Peter_SSs]

Just be aware that unless your data is sorted by column A (the column you are using to decide on duplicates), Remove Duplicates is unreliable. Try this example to see

 

[VbaHell]

you make perfect sense here and i am using your code as it's far quicker than my solution
Thanks for your help

 

[VbaHell]

Hi Peter

Thank for the insight, i did not know that the data remove duplicates could produce errors

 

[Peter_SSs]

Hi Peter

Thank for the insight, i did not know that the data remove duplicates could produce errors

So ..
- Is your data sorted that way?
- If not, can it be sorted that way or do you want it to retain the current order apart from the deleted rows?

I'm asking, because there are other ways to delete the the duplicates quickly without sorting first if required. Sorting would be simplest though.

 

[VbaHell]

Peter

I can't sort the data as it must stay in this order to allow the last lines imported which could be duplicates to be deleted first

 

[Fluff]

How about

Sub delDupes()
   Dim Cl As Range, Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Nothing
         Else
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub