CODEWITHSUNDEEP
excelduplicates
theforestecologist
theforestecologist

Reputation: 4917

How to remove duplicate values in an individual column for multiple columns at once

How do I remove duplicate values within individual columns across all of my columns at once?

If I select the Remove Duplicates tool in Excel with multiple columns selected, it assumes I'm looking for duplicates across columns.

What I want is to remove duplicates in individual columns, but I don't want to have to click each of my columns individually.

What I've Tried:

  1. Record Macro. The results:

    ActiveWindow.ScrollColumn = 1
Columns("A:A").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$582").RemoveDuplicates Columns:=1, Header:=xlYes

    As you can see, for some reaosn it auto selects column "A". I don't know how to make it generically select the letter of the column I've selected. Sooo...

  2. I tried the following:

    Col = Split(ActiveCell(1).Address(1, 0), "$")(0)
Application.CutCopyMode = False
ActiveSheet.Range("Col1:Col1581").RemoveDuplicates Columns:=1, Header:=xlYes

    But it also does not work.

How would I go about doing this??

I'm using MS Excel 2010 (Professional Plus)

Upvotes: 0

Views: 1987

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

To simply loop through columns and remove duplicates from that column, say A through Z, you can do this:

Sub removeDups()
Dim col As Range
For Each col In Range("A:Z").Columns
    With col
        .RemoveDuplicates Columns:=1, Header:=xlYes
    End With
Next col
End Sub

You can tweak that as needed, whether setting a range to equal your selection, or extending the range to, say, AZ.

Upvotes: 2

Related Questions