Remove Duplicates using vba

Question

I want to delete duplicate entries from each column, I am successful in doing it for one column C but I want to do it from A to Column Z. I have tried to change myCol to variable long & wrote column number but it is giving me an error (so that I can run number loop). Here is my code:

Sub test()
Dim myCol As String

myCol = "C"

With Sheets("Union")
    lastrow = .Range(myCol & .Rows.Count).End(xlUp).Row
    .Range(myCol & "1:" & myCol & lastrow).RemoveDuplicates Columns:=1, Header:=xlYes
End With

End Sub

I have a small doubt.. You want to remove duplicates from say [1.] Range("A1:O23") or [2.] From each column individually? If it is "1" then the complete row will be matched with the other rows... — Siddharth Rout
– Siddharth Rout, Commented Aug 16, 2019 at 7:48

DDV · Accepted Answer · 2019-08-16 09:35:08Z

3

You could try creating a For loop accounting for A-Z as integers. I am not sure that this is the most efficient solution, but it could work for you. See below.

Option Explicit

Sub test()

    Dim myCol As Long
    Dim lastrow As Long

    For myCol = 1 To 26
        With Sheets("Union")
            lastrow = .Cells(.Rows.Count, myCol).End(xlUp).Row

            If lastrow >= 3 Then
                .Range(.Cells(1, myCol), .Cells(lastrow, myCol)).RemoveDuplicates Columns:=1, Header:=xlYes
            End If
        End With
    Next myCol

End Sub

EDIT: Tested, and working as required.

SECOND EDIT: Added in a lastrow check to ensure there is some data in the column before trying to remove duplicates. 3 because that would mean at least 2 entries in that column, the minimum for there to be a need to check for duplicates.

edited Aug 16, 2019 at 9:35

answered Aug 16, 2019 at 7:36

DDV

2,4155 gold badges16 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Umar Farooq Over a year ago

Dear, it is giving me Apllication defined error on this line ".Range(.Cells(1, myCol), .Cells(lastrow, myCol)).RemoveDuplicates Columns:=1, Header:=xlYes"

DDV Over a year ago

If you use Debug.Print for lastrow and myCol what are they when you receive this error?

Umar Farooq Over a year ago

my Col is 2 last row is 1, actually, at some columns, I have data and some columns I don't have data i think this may be the issue

DDV Over a year ago

That is exactly your issue my friend. I will adjust the code above to try take this into account.

JvdV Over a year ago

@Dean, header is row 1, data is row 2 and 3. So lastrow must be either >=3 or >2. Check with this data: Header1,A,A from row 1:3. :). It will currently skip a column with just two rows of data and a header.

|

Collectives™ on Stack Overflow

Remove Duplicates using vba

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related