Excel Cell, Row and Column Reference Secrets in VBA Code

Code VBA to Refer to Excel Cells, Rows and Columns Using Numbers

My favourite way of referring to ranges of Excel cells in VBA code is to use the Cells property and numbers. For example, the following code refers to cell C4 in the active sheet:

Cells(4, 3)

 

As this does not require any column letters it is very easy to substitute variable names in place of any numerical values, which means I can then use loops to cycle through different cells that are adjacent to each other (a lot of VBA in Excel is repetitive looping and processing or updating cells). So I could have two variables – one for row and one for column position and substitute something like this:

Cells(lngRow, intColumn)

 

Rows Only

If I want to refer to all the cells in a specific row, then I can use this:

Rows(4)

 

That code would refer to every cell in row 4.  Again I could substitute a variable like this:

Rows(lngRow)

 

Columns Only

If I want to refer to all the cells in a specific column, then I can use this:

Columns(3)

 

That code would refer to every cell in column C.  Again I could substitute a variable like this:

Columns(intColumn)

 

Interestingly, because columns are referred to with letters in the Excel User Interface I can use that in my VBA. So another way to refer to column C in my VBA could be:

Columns("C")

 

However, in that code it would be harder to substitute a numerical variable where I could loop through multiple columns. On the other hand to refer to more than one adjacent column I could use the letters. So to refer to columns C, D and E, I could use:

Columns("C:E")

 

Again, in that code would be impossible to substitute a numerical variable. There is a way to achieve this though using numerical variables. The secret is to use a range object and then supply the column numbers as the arguments. So the code below is the equivalent of using Columns(“C:E”):

Range(Columns(3), Columns(5))

 

With the is code I can now substitute numerical variables. So I could use:

Range(Columns(intFirstColumn), Columns(intLastColumn))

 

All of the code samples above refer to actions within the ActiveSheet.  If you need to refer to a specific sheet, then every object in the code needs to be fully qualified. So in order for the code immediately above to be used to refer to a sheet named Expenses I would need to use:

Worksheets("Expenses").Range(Worksheets("Expenses").Columns(intFirstColumn), _
Worksheets("Expenses").Columns(intLastColumn))

 

Of course this I could simplify the fully qualified code using a With..End With statement, so the example below shows me changing the value of every cell in columns C to E to the text “Chris”:

intFirstColumn = 3
intLastColumn = 5
With Worksheets("Expenses")
  .Range(.Columns(intFirstColumn), .Columns(intLastColumn)).Value = "Chris"
End With

,

Comments are closed.