Delete Duplicates in a Column, For all Columns

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,187
Office Version
2007
Platform
Windows
If the "Matrix" sheet exists, then you just need this:

VBA Code:
Sub RemoveDuplicate()
  Dim i As Long
  For i = 1 To Sheets("Matrix").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    Sheets("Matrix").Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
  Next
End Sub
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
742
If the "Matrix" sheet exists, then you just need this:

VBA Code:
Sub RemoveDuplicate()
  Dim i As Long
  For i = 1 To Sheets("Matrix").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    Sheets("Matrix").Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
  Next
End Sub
This is working...Thank you
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,187
Office Version
2007
Platform
Windows
Glad we could help & thanks for the feedback
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
742
Glad we could help & thanks for the feedback
Well...it's not working
Here is what I have:


Sub RemoveDuplicate()

'Application.ScreenUpdating = True
Application.DisplayAlerts = False

For i = 1 To Sheets("Matrix").Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
Sheets("Matrix").Columns(i).RemoveDuplicates Columns:=1, Header:=xlYes
Next


'Application.ScreenUpdating = False
Application.DisplayAlerts = True

End Sub

When I run it now, for some reason, it's moving all rows up one. Is it possible to have the code start on Row5, for every single column it goes thru?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
How about
VBA Code:
Sub Guzzlr()
   Dim i As Long, UsdCols As Long
   With Sheets("Matrix")
      If .Visible Then
         UsdCols = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column
         If UsdCols < 5 Then Exit Sub
         For i = 5 To UsdCols
            .Range(.Cells(5, i), .Cells(Rows.Count, i).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlYes
         Next i
      End If
   End With
End Sub
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
742
How about
VBA Code:
Sub Guzzlr()
   Dim i As Long, UsdCols As Long
   With Sheets("Matrix")
      If .Visible Then
         UsdCols = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column
         If UsdCols < 5 Then Exit Sub
         For i = 5 To UsdCols
            .Range(.Cells(5, i), .Cells(Rows.Count, i).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlYes
         Next i
      End If
   End With
End Sub
Almost, If I put 47 in E5 and E6, the 47 remains a duplicate. all other duplicates below E6 are deleted as should be
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
If row 5 is not a header row then change xlYes to xlNo
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,187
Office Version
2007
Platform
Windows
Is it possible to have the code start on Row5
Try this
VBA Code:
Sub RemoveDuplicate()
  Dim i As Long, sh As Worksheet
  Set sh = Sheets("Matrix")
  For i = 1 To sh.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    sh.Range(sh.Cells(5, i), sh.Cells(Rows.Count, i)).RemoveDuplicates Columns:=1, Header:=xlYes
  Next
End Sub
 

Forum statistics

Threads
1,089,220
Messages
5,406,925
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top