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.
Range Reference with VBA
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.
Another way to refer to a range is with a named range. In VBA a range can be named in the named range box.
The named range is called ItemList. Refering to the named range in VBA can be done in a couple of ways.
Range(“ItemList”)
using the range method or;
[ItemList]
Using the square brackets method. As both methods are bound by the name which is produced outside of VBA I prefer the second method as I am lazy and less typing.
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.
Dim lw As Integer
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
The following VBA example is the same procedure using the Cells method.
Dim lw As Integer
Here is a VBA example using the first method where the last used row is trapped and used to enter a number in the cells from row 2 to bottom cell of column B.
Sub Test() 'Pass the Last Row (lr) from Column A to Column B.
Dim lr as long
Range("B2:B" & lr).value = 123
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.
Using the Cells Method.
Sub Test() Trap the last row in Columm A.
Dim lr as long
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 Test() Trap the last row in Columm A.
Dim lr as long
lr = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & lr).Interior.Color = vbGreen
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
Cells(1, lc).Interior.Color=vbBlue
Just add + 1 to the end of the lc to find the first blank column.
lc = Range("IV1").End(xlToLeft).Column + 1
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.
Dim lc As Integer
Dim lw As Integer
'Excel VBA for last used row and column.
lc = Sheet1.Range("IV1").End(xlToLeft).Column
Range(Cells(1, lc), Cells(lw, lc)).Interior.Color = vbYellow
The above with trap the ranges for the last row (lw) and the last column (lc) and colour the cells yellow.
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
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.
Using the Resize Range 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.
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
Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, lc).Interior.Color=vbCyan
Or to do the same things without a variant;
Sub LrVariant4() 'Excel VBA for Dynamic capture without variant for data which is tabular
The Excel file below shows some of the above VBA procedures.
