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

excelos

Active Member
Joined
Sep 25, 2011
Messages
310
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Try:

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
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])
 

Forum statistics

Threads
1,082,587
Messages
5,366,481
Members
400,893
Latest member
aoreilly2

Some videos you may like

This Week's Hot Topics

Top