How to Create Dynamic Range Based on Cell Value in Excel VBA?

Dynamic ranges in excel VBA provide us with the flexibility to define ranges beyond the specific values. The dynamic range enables the program to perform transformation and data management steps with ease without the worry of changing the range of the data set. The dynamic range in excel VBA promotes code reusability and does not make the programming logic to be redundant. By utilizing the range object and cells object, the dynamic ranges in Excel VBA can be defined and created. There are many ways in which dynamic ranges can be created. Following are the methods listed below:

• Variable initialization to the range and cell object.
• Create a Dynamic range passed as the value in another excel cell.

The following examples help in illustrating the above methods,

Variable Initialization to Range and Cell Object

With the change in the size of the data, it would be useful if the range object that is used to refer to the ranges can be modified. The following code helps in counting the last row and column in an excel as shown below

Sub test()

Dim EndR As Integer

Dim lastCl1 As Integer

Sheet2.Activate

EndR = Sheet2.Range("A1").End(xlUp).Row

lastCl1 = Sheet2.Range("XFD1").End(xlToLeft).Column

MsgBox EndR

MsgBox lastCl1

End Sub

The above code can be used along with the combination of range and cell object to create a dynamic range. Following is the code for Dynamic range as shown below

Sub test()

Dim EndR As Integer

Dim lastCl1 As Integer

Sheet2.Activate

EndR = Sheet2.Range("A1048576").End(xlUp).Row

lastCl1 = Sheet2.Range("XFD" & EndR).End(xlToLeft).Column

MsgBox EndR

MsgBox lastCl1

Set DynamicRange = Sheet2.Range(Cells(1, 1), Cells(EndR, lastCl1))

MsgBox "Range is " & DynamicRange.Address

End Sub

Code:

When the above code is executed in the VBA, following is the result:

Create a Dynamic Range Passed as Value in Another Excel Cell

Let us take another example where we take the below data set and select multiple rows for the below data set, we have an empty cell value to assign the ranges with the value 

let us assign the range value as 3. The below VBA codes take a value from the active sheet and assign it to the range object to create a dynamic range. 

Sub test2()

Dim EndR As Variant

Sheet2.Activate

EndR = Sheet2.Range("E3").Value

Set DynamicRange = Sheet2.Range("A1:B" & EndR)

DynamicRange.Select

End Sub

Code:

Following would be the output as shown below,

Let us take another example where we take the below data set and select multiple rows for the below data set using two cell values instead of one cell, we have an empty cell value to assign the ranges with the value.

Let us assign the range value as Starting range as A1 and the ending range as B4. The below VBA codes take a value from the active sheet and assign it to the range object to create a dynamic range.

Sub test2()

Dim EndR As Variant

Dim Endcl As Variant

Sheet2.Activate

EndR = Sheet2.Range("E3").Value

Endcl = Sheet2.Range("E4").Value

Set DynamicRange = Sheet2.Range(EndR & ":" & Endcl)

DynamicRange.Select

End Sub

Code:

Following would be the output as shown below,

Summary

• The above codes help in understanding how to create a dynamic range in VBA.
• Dynamic range helps in boosting code reusability and helps in effective data management.