Remove duplicate per column in a multi columns range

[arishy]

For each multi row column in a range I want to loop each column and remove duplicate, At the same time compress the column by removing the duplicate.

Each Column has:

HEADER1

Value1

Value2

Value2

Value3

Value1


The cleanup column:

HEADER1

Value1

Value2

Value3


The Clean up column replaces the original data.


Then loop the next column

 

[shrivallabha]

Hi arishy,


What version of Excel do you use, if it is 2007 or higher then there's option named remove duplicates which will do it for you.


Test this code on a backup. Please adjust the range A1:F7 to suit.

[pre]

Public Sub RemDupes()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1:F7").Columns
c.RemoveDuplicates 1, xlYes
Next c
Application.ScreenUpdating = True
End Sub

[/pre]

 

[arishy]

worked like a charm, and yes I am using 2013.

This is a master piece....

Thank you.