Referring to Ranges in VBA

What is the best way to refer to a range in Excel VBA?  The answer is it totally depends on your objective, there are many different ways to refer to a range from within VBA. What I would suggest is that if you have to refer to a range you do not need to select it first.  The macro recorder will select ranges as a point of reference but as a general rule you rarely need to select anything in Excel's VBA language.

The following YouTube video outlines how to trap a dynamic range both vertical and horizontal from scratch. The Excel file used in the video is here.

Dynamic Ranges.xlsm

 
 

As shown below there are many ways to go when referring to ranges. Some are better than others.

Photo by Daniele Levis Pelusi on Unsplash

The following is probably the most typical way to refer to a range in VBA.

Range("A1").value=123

You can also refer to a cell address the following way with VBA.

Cells(1,1).value=123

The method above works on the following methodology Cells(Row Number, Column Number) so Cells(1, 1) is the same as typing A1 in an excel formula.

The advantage of the above examples is you can pass variables to them. In many circumstances I prefer to use the square brackets[]. The following is the same as the examples above;

[A1]=123

The advantage is there is not much to type when compared with the first two examples. The disadvantage of referring to a range inside the square brackets [ ] is that you cannot add a Variable within the brackets. Here is a VBA example using the first method;


Sub Test() 'Pass the Last Row (lr) to Column B.
Dim lr as long
lr=range("A" & rows.count).end(xlup).row
Range("B2:B" & lr).value=123
End Sub

The procedure will make cells B2 to the last used row in Column A equal to 123. So in circumstances where I know the range will always be static, I use the square brackets [ ] method. If a variable is required then the Range or Cells method is used.

Trapping Dynamic Ranges in VBA

The Range Object is probably the Object you will utilize the most in your Excel VBA code.  Mostly the nature of the range will be changing as different length data sets are imported to Excel each day, week, month.  As such trapping a variable range length is in my opinion the most important task in VBA.  Here are some examples of trapping dynamic ranges using a range of methods.  Commit these to memory and you have mastered an important lesson in developing solid VBA skills.

Sub LastUsedRow() 'Excel VBA for Last cell with data in Column A.
Dim lw As Integer
lw = Range("A1048576").End(xlUp).Row
End Sub

The following VBA example will do the same for larger data sets.


Sub LastUsedRow2() 'Excel VBA for Last cell with data in Column A.
Dim lw As Integer
lw=Range("A" & Rows.Count).End(xlUp).Row
End Sub

The following VBA example is the same procedure using the Cells method.

Sub LastUsedRow3()
Dim lw As Integer
lw = Cells(Rows.Count, 1).End(xlUp).Row
End Sub


Trapping the last row of a range based on another columns last row is a bit more involved.

Sub LastUsedRow4()
Dim rng As Range

rng = Range("A2" & ":C" & Range("C" & Rows.Count).End(xlUp).Row)
rng.interior.color = vbyellow
End Sub

This traps a range from A2 to the last use row in column C.

Last Used Row in Column Plus One Row

If you want to get the first blank row in a particular column, that entails adding one row to the procedure from above.

Sub LastRow() 'Excel VBA Last Used Cell plus one (first Blank cell)
Dim lr As Integer
lr=Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & lr).Interior.Color=vbGreen 'Makes the first blank row in Col A Green.
End Sub

Last Used Column in a Row

Trap the last used column in Excel (typically the last heading) in a particular row with data using VBA. The following will colour the last used column in blue.

Sub LastUsedCol() 'Excel VBA Last Used Column starting in Row 1.
Dim lc As Integer
lc=Range("IV1").End(xlToLeft).Column
Cells(1, lc).Interior.Color=vbBlue
End Sub

Just add + 1 to the end of the lc to find the first blank column.

lc=Range("IV1").End(xlToLeft).Column + 1

Trapping Non Consecutive Ranges

Sometimes you may want to trap a range that doesn’t include all of the columns. Provided the ranges are of the same length this can be done with VBA. Let’s choose a range such as rows 2 to 100 in Columns A, B, C, E, F, J, K and O, P. The following will capture all of these ranges.

Sub UnionRng() 'Mulitple cols same length.
Dim rng As Range

Set rng = Union(Range("A2:C100"), Range("E2:F100"), Range("J2:K100"), Range("O2:P100"))
rng.copy Sheet1.Range(“A1”)
End Sub

You can also use some of the above lessons to add a variable last row instead of 100 into the coding.

Bringing the Last Used Row and Last Used Column Together

Below is a practical Excel VBA example of how you might trap and use the above procedures together to copy data from the first row to the last row of the last used column. This sort of dynamic data capture is extremely useful when you are unsure of the size of the data which will be imported periodically.


Sub LastUsedCol2() 'Last Used Column starting in Row 1, Capture Range.
Dim lc As Integer
Dim lw As Integer
'Excel VBA for last used row and column.
lw=Sheet1.Range("A" & Rows.Count).End(xlUp).Row
lc=Range("IV1").End(xlToLeft).Column
Range(Cells(1, lc), Cells(lw, lc)).Interior.Color=vbYellow
End Sub

Trapping the True Last Row and Column

There are times when you may not know where the last row and Last column are going to appear. This Excel VBA procedure will trap both. The wildcard character (*) is used to locate any cell which is not blank.

Sub FinditAll()
Dim lc As Long
Dim lr As Long
'Excel VBA for real last used column and row.
lc=Cells.Find("*", , , , xlByRows, xlPrevious).Column
lr=Cells.Find("*", , , , xlByRows, xlPrevious).Row
MsgBox "Row " & lr & ": Column " & lc
End Sub

Trapping Ranges without Variants

The full used range can be trapped without the use of variants. The following line of code is longer than if you were to use a variant. However there is no Dimension (Dim) Statement line and you do not require a line to assign a number to an integer for the last row. So the procedure can be done in one line rather than three. Some prefer to trap the range with a variant however I like to keep the code as brief as possible as the following is understandable and it requires less typing.


Sub LrNoVariant() 'Excel VBA for Used Range in Col A no Variant
[A1048576].End(xlUp).Interior.Color=vbRed
End Sub


The code above effortlessly traps the last row without a variable.

Later Versions of Excel

The following will trap a dynamic range in Column A but is not restricted to 65536 Rows as was the limitation in Excel 2003 and earlier.

Sub GetRng() 'Excel VBA Good for later versions of XL.
Range("A1", Range("A" & Rows.Count).End(xlUp)).Interior.Color=vbGreen
End Sub

Using the Resize method

This method would be used when you know that a particular column will be populated while others may be potentially sparsely populated at the bottom of the range. This uses the populated column to trap the last used row then it captures the columns to the right of the selection using the Resize range method.


Sub LrNoVariant2() 'Excel VBA for trapping Used Range in Col A no Variant using resize to increase range.
Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 5).Interior.Color=vbMagenta
End Sub

A Dynamic Resize Based on the Last Used Column

Here we can trap the last used row in column A and resize the range to include all of the data up to the last used column in the Range.


Sub LrVariant3() 'Excel VBA for Dynamic capture with variant for Last Used Column.
Dim lc As Integer
lc=Range("IV1").End(xlToLeft).Column
Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, lc).Interior.Color=vbCyan
End Sub

Or to do the same things without a variant;


Sub LrVariant4() 'Excel VBA for Dynamic capture without variant for data which is tabular
Range("A1").CurrentRegion.Interior.Color=vbYellow
End Sub

The Excel file below shows some of the above VBA procedures.