PDA

View Full Version : Macro for Depulicating Multiple Columns in Excel



charliea
08-13-2015, 02:49 AM
Hey there,

Complete VBA newbie. I have been searching for weeks on how to do this, sampling other peoples VBA on different websites.

But none of the solutions suggested seem to work.

I have 20,0000 columns in my spreadsheet and I want to remove duplicates from each column, independent of the rest of the columns. So it would be like selecting on one column and using the Remove Duplicates tool, and then moving onto the next column and so forth.

Could anybody quickly write a quick VBA Macro to solve this issue? My range of my spreadsheet is about 30 rows down and 20,0000 columns across.

As I'm sure you can tell, my Excel skills are pretty weak.

Thank you in advance,
Charlie

p45cal
08-13-2015, 03:21 AM
try:
Sub blah()
On Error GoTo myexit
Application.ScreenUpdating = False
For Each colm In ActiveSheet.UsedRange.Columns
colm.RemoveDuplicates Columns:=1, Header:=xlYes
Next colm
myexit:
Application.ScreenUpdating = True
End SubThis works on the currently active sheet. It assumes the columns each have a header.
You're doing well to have 200000 columns when Excel's limit is 16384