## 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 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;

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.

## 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.

Dim lw As Integer

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

Dim lw As Integer

**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.

Dim lr As Integer

Range("A" & lr).Interior.Color=vbGreen 'Makes the first blank row in Col A Green.

**Last Used Column ina 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=Range("IV1").End(xlToLeft).Column

Range(Cells(1, lc), Cells(lw, lc)).Interior.Color=vbYellow

**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.

Dim lc As Long

Dim lr As Long

'Excel VBA for real last used column and row.

lr=Cells.Find("*", , , , xlByRows, xlPrevious).Row

MsgBox "Row " & lr & ": Column " & lc

**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.

The code above is restricted to 65536 rows. Most Excel files will not exceed that but later versions of Excel have the capacity to accept more rows of data.

**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 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.

**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.

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 tabularThe Excel file below shows some of the above VBA procedures.