Delete Duplicates in a Column, For all Columns

[Guzzlr]

Hello All
I have this:

Option Explicit
Dim sht As Worksheet

Sub RemoveDuplicate()

    For Each sht In ActiveWorkbook.Worksheets
        If sht.Visible And sht.Name = "Matrix" Then
            sht.Activate
            ActiveSheet.Range("CN:CN").RemoveDuplicates Columns:=1, Header:=xlYes
        End If
    Next sht
    
End Sub

This is working to find and remove duplicates in Column CN. However, I don't know how to loop thru all columns (beginning with column E) to the end of columns where no data exist anymore, to find duplicates in each individual column.
Currently, if I want to find duplicates in Column L, then I change the CN to L and run the macro again...and so on.
Thanks for the help

 

[DanteAmor]

Try this.
It is not necessary to go through all the sheets.
If the "Matrix" sheet is hidden, it is not necessary to make it visible.

Sub RemoveDuplicate()
  Dim lc As Long, i As Long
  If Evaluate("ISREF('" & "Matrix" & "'!A1)") Then
    With Sheets("Matrix")
      lc = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Column
      For i = 1 To lc
        .Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
      Next
    End With
  End If
End Sub

 

[Fluff]

As you are only looking for one sheet there is no need to loop through all the sheets, how about

Sub RemoveDuplicate()
   Dim i As Long
   With Sheets("Matrix")
      If .Visible Then
         For i = 5 To 92
            .Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
         Next i
      End If
   End With
End Sub

 

[Guzzlr]

Try this.
It is not necessary to go through all the sheets.
If the "Matrix" sheet is hidden, it is not necessary to make it visible.

Sub RemoveDuplicate()
  Dim lc As Long, i As Long
  If Evaluate("ISREF('" & "Matrix" & "'!A1)") Then
    With Sheets("Matrix")
      lc = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Column
      For i = 1 To lc
        .Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
      Next
    End With
  End If
End Sub

This did not work? I still have duplicates in column's CN and DI. I did change dim lc to kc....I keep getting mixed up if it's an L or a 1
Thanks for the help

 

[Guzzlr]

As you are only looking for one sheet there is no need to loop through all the sheets, how about

Sub RemoveDuplicate()
   Dim i As Long
   With Sheets("Matrix")
      If .Visible Then
         For i = 5 To 92
            .Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
         Next i
      End If
   End With
End Sub

This did remove duplicates to column CN, but did not catch the columns all the way to Column DI
UPDATE: I just changed the 92 to 114, and it found the duplicates to DI.
Is there a way to go to the end, with out having to manually add the column number, should I add more data and more columns. Currently, the data set goes to DI. However, I may add more columns out further.
Thanks for the help

 

[Fluff]

If you want it to go out to DI change the 92 to 113

 

[Guzzlr]

If you want it to go out to DI change the 92 to 113

Is there a way to have it go to the end of columns with data, with out having to manually change the code, as I add more columns?
Thanks

 

[DanteAmor]

This did not work? I still have duplicates in column's CN and DI. I did change dim lc to kc....I keep getting mixed up if it's an L or a 1
Thanks for the help

It is a letter L.
Did you modify the macro?

 

[Joe4]

Is there a way to have it go to the end of columns with data, with out having to manually change the code, as I add more columns?

Do you have a header/title row? If so, which row is it?
Or do you have some row which will always have data in it that we can look in to see which column is the last one with data in it?

 

[Fluff]

How about

Sub RemoveDuplicate()
   Dim i As Long, UsdCols As Long
   With Sheets("Matrix")
      If .Visible Then
         UsdCols = .Range("1:1").Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column
         If UsdCols < 5 Then Exit Sub
         For i = 5 To UsdCols
            .Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
         Next i
      End If
   End With
End Sub