Remove Duplicates Column By Column and Issues with Macro Recording

[pikachu]

Hi everyone! I literally have a headache from researching how to do this and finding that most suggestions just aren't working for me. I have a spreadsheet with values in a range of data A2:LI2590. There are many blanks in the spreadsheet and all I'm trying to do is remove the duplicates (or put another way, find unique values within that range). I've gotten really close, but so far my blunders have been:

• Using the Record Macro tool doesn't work. The spreadsheet is xlsm and I go through the steps as I've seen them done online, but when I go to my VBE the only thing that shows up is the description I gave for the macro and sometimes a line showing I selected A:A
• Another VBA code I found seemed solid, but I got a a Runtime error 429 that mentioned "Scripting.Dictionary." Not sure what that was.
• Other solutions are there but not feasible because Excel slows down way too much when running it (an index/match formula to pull unique values, for instance, just runs Excel to the ground).

I'm running Excel for Mac btw if that makes a difference. Thanks for any help you can offer.

 

[Eric W]

Hi pikachu,

Using the Record Macro tool could work, if you have the right procedure. However, I confess I think it would be long and complicated to do it that way.

"Scripting.Dictionary" is a very powerful data structure, but it's only available on Windows, and you're on a Mac. Sorry!

There are formula solutions, but as you found out, they can really slow down Excel.

So here's a VBA option. I am NOT on a Mac, and I can't really troubleshoot it if it doesn't work on your PC. However, it works on my PC, and I don't believe it uses any Windows-specific functions.

Sub GetDupes()
Dim r As Long, c As Variant, MyRange As Variant

    r = 1
    Cells(1, "LK") = "List"
    
    MyRange = Range("A2:LI2590").Value
    For Each c In MyRange
        If c <> "" Then
            r = r + 1
            Cells(r, "LK") = c
        End If
    Next c
    
    Range("LK:LK").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "LM1"), Unique:=True
    Columns("LK:LK").Delete Shift:=xlToLeft
    
End Sub

Your range is in red. I used LK as the column where you want the unique list, and the next column (LM) is a helper column.

Hope this helps!

 

[pikachu]

YES! Looks like this worked Eric. Thank you sooooo much.

 

[Eric W]

Glad to help!