To use a range or a single cell as a variable, first, you need to declare that variable with the range data type. Once you do that you need to specify a range of a cell to that variable using the range object. This also gives you access to all the properties and methods that you can use with a range.
- Use the “Dim” keyword and enter the name of the variable.
- Declare the keyword as Range.
- Type the name of the variable.
- Specify the range that you want to assign to the variable.
Set Selection to Range Variable
You can also use the selected range to a variable and then that variable to access properties and methods. Consider the following code.
Sub vba_range_variable() Dim rng As Range Set rng = Selection Selection.Clear End Sub
But there is one thing that you need to take care that when you are selecting an object other than a range in that case VBA will show you an error.
But this is also a dynamic method where you don’t need to change the range again and again.
Copy a Range using Variable
Once you declare a variable as a range you can copy that range using that variable name as well. Let say you have declared the range A1:A10 as a variable rng (above example), you can copy it using the following code.
Sub vba_range_variable() Dim rng As Range Set rng = Range("A1:A10") rng.Copy End Sub
Using Range Variable in a Function
You can also use a range variable to refer to a range in a function while writing a macro. Consider the following code.
Sub vba_range_variable() Dim iNames As Variant Dim rng As Range Set rng = Range("A1:A10") Range("B1") = WorksheetFunction.Max(rng) End Sub
It takes values from the range A1:A10 and as you have to use the variable “rng” we have used the variable in the to the max value to the from it.
Related: How to use WorksheetFunction in VBA
Count Number of Rows and Columns
As I have already said, once specify a variable as a range you will also get all the properties and methods to access. Imagine if you want to count the rows and columns. Following is the code that could be used.
Sub vba_range_variable() Dim rng As Range Set rng = Range("A1:A10") MsgBox "This range has " & _ rng.Rows.Count & " row(s) and " & _ rng.Columns.Count & " coulmn(s)." End Sub
When you run this code, it shows a message with the count of rows and columns from the range that you have specified in the variable.
Defining Worksheet Along with the Range
In all the examples above, we have used range without mentioning the worksheet, so when you run that code, VBA will always refer to the range in the active sheet. But if you want to refer to a specific worksheet then you need to use the worksheet object along with the range.
Dim rng As Range Set rng = Worksheets("Sheet1").Range("A1:A10")
⇠ Back to What is VBA in Excel
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder – VBA Interview Questions – VBA Codes