VBA Range Object Variables (Dim / Set Range)
In this Article
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:
1 2 3 4 |
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:
1 2 3 4 5 6 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
1 2 3 4 5 6 7 8 |
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:
1 2 3 4 5 6 7 8 |
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!