I entered a piece of code to find column by name and I want to use this variable later in the code.
ColTax = Application.Match("Tax", Sheets("DATA").Rows(1), 0)
However, I have a problem with updating the range below:
Set rng = Range("I3", Range("I" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
How do I update "I" to reflect ColTax?
Use Cells instead of Range, since Cells accepts a numeric column index.
Dim ColTax As Variant ' Application.Match can return an error, so must be a Variant
ColTax = Application.Match("Tax", Sheets("DATA").Rows(1), 0)
If IsError(ColTax) Then
' The match failed, so ColTax is an error, not the column number
Exit Sub
End If
With Sheets("Data")
Set rng = .Range(.Cells(3, ColTax), .Cells(.Rows.Count, ColTax).End(xlUp)).SpecialCells(xlCellTypeVisible)
End With
Note that SpecialCells(xlCellTypeVisible) will throw an error if there are no visible cells. Safest would be to enclose the Set rng line with On Error Resume Next and On Error GoTo 0, and then test If not rng Is Nothing.
On Error Resume Next
Set rng = ....
On Error GoTo 0
If Not rng Is Nothing Then
' keep processing
End If
