EDUCBA

Home VBA VBA Resources VBA Coding VBA Variable Range
 

VBA Variable Range

Madhuri Thakur
Article byMadhuri Thakur

VBA Variable Range

Excel VBA Variable Range

In this article, we will see an outline on Excel VBA Variable in Range. But using a range property means a user has to know which range to use that’s where range as variable comes in. In VBA we have a data type as a range that is used to define variables as a range that can hold a value of the range. These variables are very useful in complex programming and automation. Often variable range is used with the set statements and range method. Set statements are used to set a variable to a range of particular size and range property method is used to get the values already in that range or to replace them with another value. First of all, let us be clear of what is a range?

 

 

What is Range?

A range is a group of cells that can be from a single row or from a single column also it can be a combination of rows and columns. VBA range is a method that is used to fetch data from specific rows columns or a table and it is also used to assign the values to that range.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

We will first begin with the basic range property method and then with the variable range. Will all examples in a sequence we will be able to understand the relationship with the range variable, set statement and the range property method.

How to Set Variable Range in Excel VBA?

We will learn how to set a different variable range in Excel by using the VBA Code.

You can download this VBA Variable Range Excel Template here – VBA Variable Range Excel Template

VBA Variable Range – Example #1

Let us begin our first example for the variable range in the simplest way which is by using the range property method. In this example, we will assign a few cells with our own custom values using the range method. Let us follow the below steps.

Step 1: Open a module from the Insert menu as shown below.

Insert Module

Step 2: Write the subprocedure of VBA Variable Range.

Code:

Sub Example1()

End Sub

VBA Variable Range Example1-1

Step 3: Now here is a key part to remember that whatever procedure we write VBA executes it in the active sheet or the active worksheet, so in order to run the different procedures on the different worksheets we need to activate them first using the Activate method.

Code:

Sub Example1()

Worksheets("Sheet1").Activate

End Sub

Activate method Example 1-2

Step 4: Now let us begin the simple operation by assigning a custom value to the cell of the worksheet 1 using the range property method.

Code:

Sub Example1()

Worksheets("Sheet1").Activate
Range("A1:C3").Value = " EDUCBA "

End Sub

worksheet 1 Example1-3

Step 5: Run the code by pressing the F5 key or by clicking on the Play Button. Once we execute the code we can see the value in Sheet 1 as follows.

Sheet 1 Example1-4

We can see that in our code we chose the cells from A1:C3 to have these custom values, this example was the basic representation of what we can do with the range property method.

VBA Variable Range – Example #2

Now let us use Range as a variable in VBA, we can use range as variable and set the variable with the values using the range property method. In this example, we will discuss how to use the range variable to fetch data from a cell and display it on the screen.

Step 1: We will begin our next example in the same module we have inserted so we do not need to insert a new module for every procedure. Below the first code initiate another subprocedure.

Code:

Sub Example2()

End Sub

VBA Variable Range Example 2-1

Step 2: Let us set a variable as a Range data type so that it can hold or store a range of values.

Code:

Sub Example2()

Dim Rng As Range

End Sub

VBA Variable Range Example 2-2

Step 3: Now we have discussed earlier the importance of activating a worksheet before using it for our example so we need to activate the Sheet 2.

Code:

Sub Example2()

Dim Rng As Range
Worksheets("Sheet2").Activate

End Sub

VBA Variable Range Example 2-3

Step 4: Before we begin to our next code let us first see what is in the value of cell A1 in sheet 2.

sheet 2 Example 2-6

Step 5: We can see that there is data in cell A1 so we will use the Set statement to assign the value of cell A1 to our variable using the range property method.

Code:

Sub Example2()

Dim Rng As Range
Worksheets("Sheet2").Activate
Set Rng = Range("A1")

End Sub

VBA Variable Range Example 2-4

Step 6: Now in order to display the value which has been stored in our variable let us use the msgbox function as follows in the image below.

Code:

Sub Example2()

Dim Rng As Range
Worksheets("Sheet2").Activate
Set Rng = Range("A1")
MsgBox Rng

End Sub

MSGBOX function Example 2-5

Step 7: Now once we execute the above code we get the following result.

VBA Variable Range Example 2-9

VBA Variable Range – Example #3

In this example, we will use a variable as a range to select a few cells from a sheet 3.

Step 1: The defining of the subprocedure will be same as for all the codes above, let us begin right below the example 3 in the same module as shown below.

Code:

Sub Example3()

End Sub

subprocedure Example 2-8

Step 2: Now declare a variable as Range data type to store the values of a range.

Code:

Sub Example3()

Dim Rng1 As Range

End Sub

VBA Variable Range Example 3-1

Step 3: Since we are going to execute the procedure in sheet 3 let us activate Sheet 3 first.

Code:

Sub Example3()

Dim Rng1 As Range
Worksheets("Sheet3").Activate

End Sub

VBA Variable Range Example 3-2

Step 4: Now use the set statement to assign a range value to our variable with the help of the range property method.

Code:

Sub Example3()

Dim Rng1 As Range
Worksheets("Sheet3").Activate
Set Rng1 = Range("A1:C3")
Rng1.Select

End Sub

VBA Variable Ranges Example 3-3

Step 5: Now let us use the select statement to select the range in the variable.

Code:

Sub Example3()

Dim Rng1 As Range
Worksheets("Sheet3").Activate
Set Rng1 = Range("A1:C3")
Rng1.Select

End Sub

VBA Variable Range Example 3-4

Step 6: When we execute the code and go to sheet 3 we can see the following result.

sheet 3 Example 3-5

VBA Variable Range – Example #4

Now let us use a variable range to assign some values to a specific range and change their font in the meanwhile.

Step 1: In the same module below example 3 we will set another procedure named Example4.

Code:

Sub Example4()

End Sub

VBA Variable Range Example 4-1

Step 2: Now set a variable as Range data type to store the values of a range.

Code:

Sub Example4()

Dim Rng2 As Range

End Sub

Declaring a variable

Step 3: Since we are going to execute the procedure in Sheet4 first let us activate Sheet4.

Code:

Sub Example4()

Dim Rng2 As Range
Worksheets("Sheet4").Activate

End Sub

VBA Variable Range Example 4-3

Step 4: Now use the set statement to assign a range value to our variable with the help of the range property method as shown below.

Code:

Sub Example4()

Dim Rng2 As Range
Worksheets("Sheet4").Activate
Set Rng2 = Range("A1:C3")

End Sub

VBA Variable Range Example4-4

Step 5: Now we will use the Value property of a range method to assign a value to that range.

Code:

Sub Example4()

Dim Rng2 As Range
Worksheets("Sheet4").Activate
Set Rng2 = Range("A1:C3")
Rng2.Value = "EDUCBA "

End Sub

VBA Variable Range Example 4-5

Step 6: Now let us change the font for the range to make it bold with the following code.

Code:

Sub Example4()

Dim Rng2 As Range
Worksheets("Sheet4").Activate
Set Rng2 = Range("A1:C3")
Rng2.Value = "EDUCBA "
Rng2.Font.Bold = True

End Sub

VBA Variable Range Example 4-6

Step 7: When we execute this code we can see the following result in sheet 4 as shown below.

sheet 4 Example 4-7

Explanation of VBA Variable Range:

Now with the above examples, we can see how useful is the variable range in our coding. Let me give an example of finding the last row in any sheet. We cannot do that with a normal range property method for that we need a variable as a range because the range property method will be static and the last row will not be identified once the data changes but using variable range we make it dynamic so that the values can also change.

How to Use the VBA Variable Range?

Let me explain another example on how to use variable range in real time code. Let us say we have different values in Sheet5 as shown below.

Sheet 5 Example 5

And I want to find which row has value “EDUCBA”, we want the row number to be displayed.

Code:

Sub Example5()

Dim ws As Worksheet
Dim FoundCell As Range
Dim WTF As String
Dim k As Integer
Set ws = Worksheets("Sheet5")
WTF = Range("A1").Value
Set FoundCell = ws.Range("A:A").Find(What:=WTF)
k = FoundCell.Row
MsgBox "Found the Value At Row " & k

End Sub

VBA Variable Range Example5-1

When we execute the code the following result is displayed.

VBA Variable Range Example 5-2

Let me explain the code, the variable Foundcell is a variable range that searches for the value of A1 in a whole range A:A and when the value is found it displays the row number with the Row method.

Things to Remember

  • Variable range is a variable with range data type.
  • It is defined as the same as the Dim Statement.
  • We use the Set statement to assign a range to the variable.
  • Range Property method is used to access the values for the range variable.

Recommended Articles

This is a guide to VBA Variable Range. Here we discuss how to set variable range in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Selection Range
  2. VBA IF Statements
  3. VBA Variable Declaration
  4. VBA Format Number