1

I'm using VBA in Excel to remove duplicate values in an active range that is ~12000 rows long, and within a single column. However, when I run the RemoveDuplicates command on my selected range, it removes the entire rows that the duplicate values are present in. My code appears below:

Sub Dedupe()

Range("B1").Select

ActiveCell.Resize(12109, 1).Select

ActiveCell.RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

My problem is that this deletes important data in the adjacent columns that I plan to dedupe next. I want to make the process loop through about 900 columns and repeat the dedupe for each column individually. Is there a way to use VBA code to remove the duplicate values in a current selection, but not delete the entire rows?

Thank you.

cyboashu
  • 10,196
  • 2
  • 27
  • 46
Westway9
  • 11
  • 1
  • 2

2 Answers2

1

Here is a script that can be setup to loop. It takes the Column letter as a variable Finds the last Row. And then removes duplicates.

You will need to change the sheet name (avoid using activesheet if you can because you could end up removing duplicates on a sheet you don't want to.

One thing to note is the cell values are brought up in the column so if A5 is lined up with B5 this will screw up your rows.

sub test()
dim myCol as string

myCol = "B"

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

end sub
Community
  • 1
  • 1
gtwebb
  • 2,981
  • 3
  • 13
  • 22
0

you can lop through columns like follows:

Option Explicit

Sub test()
    Dim col As Range

    With Worksheets("Dupes").Range("B:D") '<--| change "Dupes" with your actual sheet name and ""B:D" with your actual columns range
        For Each col In .Columns
            col.RemoveDuplicates Columns:=Array(1), Header:=xlYes
        Next col
    End With
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28