Is there a way to refer to a column by its header instead of its letters (A, B, etc) in VBA?

[excelos]

Hello

I want to work with columns in VBA, ie use them in COUNTIFS functions, create column next to a specific etc.

However, I would prefer to refer to them by their header (first top cell) and not by their reference (A, B, C, etc) because the latter is less safe in case something changes.

Is there a way to work like that?

 

[steve the fish]

Try:

Dim c As Range

With Sheets("Sheet1").Rows(1)
    Set c = .Find("hello", , xlValues, xlWhole)
    If Not c Is Nothing Then
        MsgBox c.Column
    End If
End With

 

[Rick Rothstein]

Select the entire column and then give it a Defined Name (you can do this by simply typing the name into the Name Box next to the Formula Bar). Let's say that name is "Quantity"... you can then refer to it in your code like this...

Range("Quantity")

or, in some cases, like this...

[Quantity]

When the second method does not work, the first will always work. For example...

MsgBox Application.Sum(Range("Quantity"))

or..

MsgBox Application.Sum([Quantity])