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
355
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,216
Office Version
  1. 365
Platform
  1. 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
36,675
Office Version
  1. 2010
Platform
  1. 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])
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top