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 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 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 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 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")
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel
- How to Select a Range/Cell using VBA in Excel