Reputation: 3
I found the following code to hide duplicate rows:
For i = Last_Row To First_Row Step -1
If WorksheetFunction.CountIf(Range("F" & First_Row & ":F" & i), Range("F" & i).Value) > 1 Then Rows(i).Hidden = True
Next I
The code works great, but I'm using it in a Table so I'd like to replace the fixed column "F" with a table header reference. That way if someone inserts columns it will still work. I'm struggling to find the right syntax.
My table and column is:
Range("PART_SELECTION_DATABASE[PART '#]")
Any help is appreciated.
Upvotes: 0
Views: 276
Reputation: 33692
You can use Find
function to look for the header PART '#"
.
Once it's found, you can extract the column number using FindRng.Column
.
Code
Option Explicit
Sub FindHeader()
Dim FindRng As Range
Dim HeadrStr As String
Dim Col As Long
HeadrStr = "PART '#"
Set FindRng = Cells.Find(what:=HeadrStr)
If Not FindRng Is Nothing Then . make sure Find was successful
Col = FindRng.Column ' get the column number
Else ' Find failed to find the Header
MsgBox "unable to find " & HeadrStr, vbCritical
Exit Sub
End If
For I = Last_Row To First_Row Step -1
If WorksheetFunction.CountIf(Range(Cells(First_Row, Col), Cells(I, Col)), Cells(I, Col).Value) > 1 Then Rows(I).Hidden = True
Next I
End Sub
Upvotes: 1