Delete Duplicates in a Column, For all Columns

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
943
Office Version
  1. 2016
Platform
  1. Windows
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
 
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
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
If row 5 is not a header row then change xlYes to xlNo
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top