Removing duplicates from multiple lists

[Steve_nyc92]

Hi

I searched the forum and was unable to find answer to this. I have a file with 200 columns that contain unique lists starting in column M. Each list has values that are unique from the other lists but are duplicated within the list itself.

I want to remove the duplicated values from each list so I only see the unique ones. I can’t use the excel function because it looks at all the lists as one group. I don’t want to individually remove from each list as it is over 200 columns.

Is there a macro that can do this?

 

[DanteAmor]

Try this:

Sub RemovingDuplicates()
  Dim j As Long, lc As Long
  Application.ScreenUpdating = False
  lc = ActiveSheet.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  
  For j = Columns("M").Column To lc
    Range(Cells(1, j), Cells(Rows.Count, j).End(3)).RemoveDuplicates 1, xlYes
  Next
  Application.ScreenUpdating = True
End Sub

 

[Steve_nyc92]

Try this:

Sub RemovingDuplicates()
  Dim j As Long, lc As Long
  Application.ScreenUpdating = False
  lc = ActiveSheet.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
 
  For j = Columns("M").Column To lc
    Range(Cells(1, j), Cells(Rows.Count, j).End(3)).RemoveDuplicates 1, xlYes
  Next
  Application.ScreenUpdating = True
End Sub

This worked great! Only one thing I forgot to mention is the name of the list is in row 2. So it should actually start removing duplicates in M3 to the last column/row

 

[DanteAmor]

Change this:
Range(Cells(1, j), Cells(Rows.Count, j).End(3)).RemoveDuplicates 1, xlYes

For this:
Range(Cells(2, j), Cells(Rows.Count, j).End(3)).RemoveDuplicates 1, xlYes

 

[Steve_nyc92]

Change this:
Range(Cells(1, j), Cells(Rows.Count, j).End(3)).RemoveDuplicates 1, xlYes

For this:
Range(Cells(2, j), Cells(Rows.Count, j).End(3)).RemoveDuplicates 1, xlYes

Thank you so much!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.