VBA Excel: Rename columns for easy referencing
12,100
Solution 1
Solved this problem by myself:
- mark whole column
- write the desired name in the "Name Box" in the left upper corner of Excel 2007 and press Enter
In VBA, the following code adresses the 4th row in the specific column (I named my column "Employees", according to the title of the column):
test = Sheets("Sheet1").Range("Employees").Cells(4, 1).Text
Note that when working with columns, the second parameter of Cells always has to be 1 (otherwise you leave your column)
Solution 2
Yes. To name a range you should
- select the range in excel
- click the Name box and put in the name you want to put there.
- Now in formulas you can access the cell/range by the name you defined.
Author by
derMax
Updated on June 26, 2022Comments
-
derMax almost 2 years
I am writing a script that transfers data from Excel cells to different word tables. So far, I am doing it like that:
wordDoc.Bookmarks("Editor").Range.Text = Sheets("Product Eval").Range("E" & evalRow).Text
where evalRow is computed before.
Now, I want to avoid that I have to rewrite the whole code when someone adds a column before column E. Is it possible to rename whole columns, that they keep their name even if they are moved and that I can reference a specific cell with that column name in VBA?
-
derMax over 11 yearsIs right, but answers only half of the question, therefore I accepted my own solution.
-
Bharat Sinha over 11 yearsNo issues. You can always up vote answers which you feel helped you or are useful.
-
Andrew Leach over 11 yearsI would strongly recommend naming columns according to the data they contain rather than calling an arbitrary column (say
J
) "Column2". There is scope for confusion. And if you move your columns around then "Column2" may no longer be the second set of data. -
derMax over 11 yearsThat's exactly what I did, this was just for example purpose. I modified my answer according to your suggestion.