VBA Range Variables (Dim / Set Range)

In this Article

The VBA Range Object

We have already gone over what variables and constants are, in our VBA Data Types – Variables and Constants tutorial. We are now going to look at the range object in VBA and how to declare a variable as a range object. The range object is used to denote cells or multiple cells in VBA. So, it’s very useful to use in your code.

The VBA Range Object

You can use the range object to refer to a single cell. For example, if you wanted to refer to cell A1 in your VBA code then you could use the VBA range object as shown in the code below:

Sub ReferringToASingleCellUsingTheRangeObject()

Range("A1").Value = 89

Range("A1").Font.Bold = True

End Sub

When you press run or F5 on your keyboard, to run your code then you get the following result, in your actual worksheet:

Using the Range Object in Your VBA Code

You can use the range object to refer to multiple cells or ranges. For example, if you wanted to refer to cell range (A1:C1) in your VBA code then you could use the VBA range object as shown in the code below:

Sub ReferringToMultipleCellsUsingTheRangeObject()

Range("A1:C1").Value = "ABC"

Range("A1:C1").Borders(xlEdgeBottom).LineStyle = xlDouble

End Sub

When you press run or F5 on your keyboard, to run your code then you get the following result, in your actual worksheet:
Referring to Multiple Cells and Using the Range Object in VBA

Declaring a Variable as a Range

You will need to use the Dim and Set keywords when declaring a variable as a range. The code below shows you how to declare a variable as a range.

Sub DeclaringAndSettingARange()

Dim rng As Range

Set rng = Range("A10:B10")

rng.Value = "AutomateExcel"

rng.Font.Bold = True

rng.Select

rng.Columns.AutoFit

End Sub

The result is:
Declaring and Setting a Range Variable in VBA

Selecting Specific Rows In Your Range Object

You can select specific rows within your Range Object. The code below shows you how to do this:

Sub SelectingSpecificRowsInTheRangeObject()

Dim rng As Range

Set rng = Range("A1:C3")

rng.Rows(3).Select

End Sub

The result is:

Selecting a Row in a Range Object

Selecting Specific Columns In Your Range Object

You can select specific columns within your Range Object. The code below shows you how to do this:

Sub SelectingSpecificColumnsInTheRangeObject()

Dim rng As Range

Set rng = Range("A1:C3")

rng.Columns(3).Select

End Sub

Selecting a Column in a Range Object