CODEWITHSUNDEEP
excelvba
TheRabbi99
TheRabbi99

Reputation: 3

Referencing a Column by Table Header instead of Letter

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

Answers (1)

Shai Rado
Shai Rado

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

Related Questions