Delete Duplicates in a Column, For all Columns

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
742
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
OK...I added a row, so now I want to have the code start looking in row 6. So I changed sh.Range(sh.Cells(5, i) to sh.Range(sh.Cells(6, i)...and it works. I now know what it is doing (sh.Cells(6,i) means the code begins in cell A6.
If I were to want the code to begin in say E6, I would change i=6, and keep sh.Range(sh.Cells(6, i), the same. Which I believe would make the code start looking for duplicates in cell E6.

Also...I almost forgot, I changed Header:=xl Yes to No

I believe everything is working correctly, because I now know where the code begins to look for duplicates.
thanks for all the help
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,186
Office Version
2007
Platform
Windows
Again with pleasure. Thanks for the feedback
 

Forum statistics

Threads
1,089,218
Messages
5,406,923
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top