Delete Duplicates in a Column, For all Columns

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
742
Hello All
I have this:
VBA Code:
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,186
Office Version
2007
Platform
Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
As you are only looking for one sheet there is no need to loop through all the sheets, how about
VBA Code:
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

Well-known Member
Joined
Apr 20, 2009
Messages
742
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.

VBA Code:
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

Well-known Member
Joined
Apr 20, 2009
Messages
742
As you are only looking for one sheet there is no need to loop through all the sheets, how about
VBA Code:
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
If you want it to go out to DI change the 92 to 113
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
742
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

Well-known Member
Joined
Dec 3, 2018
Messages
10,186
Office Version
2007
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
How about
VBA Code:
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
 

Forum statistics

Threads
1,089,217
Messages
5,406,916
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top