VBA Range Object Variables (Dim / Set Range)

In this tutorial we will cover the VBA Range Object Variable.

We have already gone over what variables and constants are, in our VBA Data Types – Variables and Constants tutorial. Now, 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.

Click here for more information about VBA Ranges and Cells.

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 to set the cell value and bold the cell’s text use this code:

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:

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:

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:

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!