CODEWITHSUNDEEP
vbaexcel
someone serious
someone serious

Reputation: 325

How to reference a column by its name (eg. "someColumn")?

How can I reference, say column A, which is named "apple"? (see the first image) Most answers I have found on the internet seem to include a column name somewhere in the actual column (second picture) and use the find function or match function. However, I would much rather identify a column by its name, so I can, say, do something like Range("apple1:apple10").

What I want

Not what I want

As background for this, I am making an Excel sheet with a lot of macros for a company, but am leaving soon, and want to make this sheet as unbreakable and flexible as possible, so that users can change data within the sheet and even add or remove columns. They do not know VBA and do not have the time to change my code. However, if they add a column it will mess up my macro, which currently references cells like Cells(4,7), so if you add a column before column 7, stuff will be misreferenced.

An alternative solution I have thought about is monitoring when users add or remove columns, but this seems to be too much work, can introduce too many errors, and take too long, whereas referencing columns by their names, which I can enforce users do not change, would be much simpler.

Any ideas are great. Thanks!

Upvotes: 0

Views: 615

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Turn your range into a table. Select any cell in your range:

any one cell is selected inside the data range

Then from the Home Ribbon, click Format as Table and pick any style.

"Format as Table" dropdown

Your table has headers, so tick that checkbox:

"Format as Table" dialog

Congratulations, you now have a table - you can rename it from the Table Tools Ribbon tab:

Table1

Now you can leverage the complete awesomeness of table formulas in Excel:

table formula

...and in VBA you get to work with the wonderful ListObject API: now Sheet1.ListObjects(1).DataBodyRange gets you a Range reference that contains the entire body of the table, and best of all you never need to work out the last row with data anymore.

Dim myTable As ListObject
Set myTable = Sheet1.ListObjects("Table1")
With myTable.ListColumns("Banana").DataBodyRange
    '...
End With

Upvotes: 1

QHarr
QHarr

Reputation: 84465

Used named ranges instead. These will shift if columns are inserted and remain valid.

For example A1:A7 has been named "Apple" here

Apple

If I insert a column before A:A then "Apple" will still refer to the same cells but now be B1:B7

In VBA you can access cells within this range. For example, the first cell in the "Apple" range:

Range("Apples").Cells(1,1)

These can be dynamic ranges, for example, to occupy only the populated part of a column, which is more efficient than setting for the entire column.

Read about named ranges here.

Upvotes: 1

Related Questions