VBA Excel: Rename columns for easy referencing

vba excel range named-ranges
derMax picture derMax · Aug 15, 2012 · Viewed 12k times · Source

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?

Answer

derMax picture derMax · Aug 16, 2012

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)

Related questions

Creating an Array from a Range in VBA

I'm having a seemingly basic problem but can't find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this by …

Read More
Detect if range is empty

I want to check if a range in Excel is empty. How do I write in VBA code: If Range("A38":"P38") is empty

Read More
VBA paste range

I have simple goal of copying range and pasting it into another spreadsheet. The following code below gives copies, but does not paste. Sub Normalize() Dim Ticker As Range Sheets("Sheet1").Activate Set Ticker = Range(Cells(2, 1), Cells(65, 1)) Ticker.Copy Sheets("…

Read More