How to Use Dynamic Range VBA in Excel (11 Ways)

If you are looking for some of the easiest ways to use dynamic range VBA effectively, then you are in the right place.

The best advantage of using a dynamic range in a VBA code is that you don’t have to change the code after changing your data range for different purposes. So, the code doesn’t become obsolete after changing your data table so many times.

Let’s get started with the main article to know the ways of using a dynamic range in VBA to save time and work effectively.

Download Workbook

11 Ways to Use Dynamic Range VBA

Here, I have the following data table of a company to demonstrate the ways of using a dynamic range in VBA.

For this purpose, I have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

dynamic range vba

Method-1: UsedRange Property

If you want to select the range of cells that have values in them, then you can use the UsedRange property in your VBA code.

dynamic range vba

Step-01:
➤Go to Developer Tab>>Visual Basic Option

UsedRange Property

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

dynamic range vba

After that, a Module will be created.

UsedRange Property

Step-02:
➤Write the following code

Sub dynrange1()

Worksheets("usedrange").UsedRange.Select

End Sub

Here, “usedrange” is the sheet name.
UsedRange property will select all the cells in this worksheet that have values in them.

UsedRange Property

➤Press F5

Result:
In this way, you will select the whole data range including the heading.

dynamic range vba

Related Content : Create a Dynamic Chart Range in Excel (2 Methods)

Method-2: CurrentRegion Property

If you want to select the data range including headers, then you can use the CurrentRegion property like this method.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange2()

Dim sht As Worksheet

Dim firstCell As Range

Set sht = Worksheets("current")

Set firstCell = Range("B5")

firstCell.CurrentRegion.Select

End Sub

Here, sht, firstCell are declared as Worksheet and Range respectively.

“current” is the worksheet name.
“B5” is set as firstCell because it is the starting cell of our data range.
CurrentRegion will select all the cells surrounding this B5 cell.

CurrentRegion Property

➤Press F5

Result:
In this way, you will select the whole data range including headers.

CurrentRegion Property

Method-3: SpecialCells for Dynamic Range VBA

If you want to select only the data range excluding headers, then you can use the SpecialCells property in your VBA code.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange3()

Dim sht As Worksheet

Dim Lr As Long

Dim Lc As Long

Dim FirstCell As Range

Set sht = Worksheets("special")

Set FirstCell = Range("B5")

Lr = FirstCell.SpecialCells(xlCellTypeLastCell).Row

Lc = FirstCell.SpecialCells(xlCellTypeLastCell).Column

sht.Range(FirstCell, sht.Cells(Lr, Lc)).Select

End Sub

Here, at first, we declared sht, FirstCell, Lr, and Lc as Worksheet, Range, and Long respectively.

“special” is the worksheet name.
“B5” is set as FirstCell because it is the starting cell of our data range.
Lr and Lc will return the last used row and column in the worksheet.
Finally, Range(FirstCell, sht.Cells(Lr, Lc) will give the range of the data table.

SpecialCells Property

➤Press F5

Result:
Afterward, you will be able to select the whole data range excluding headers.

SpecialCells Property

Related Content: Excel Dynamic Range Based on Cell Value

Method-4: Changing Color for Last Used Row 

Here, we will change the Fill color of the first cell in the last used row into red by using this method.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange4()

Dim Lr As Integer

Dim sht As Worksheet

Set sht = Worksheets("lastrow")

Lr = sht.Range("B" & Rows.Count).End(xlUp).Row

Range("B" & Lr).Interior.Color = vbRed

End Sub

Here, at first, we declared sht, Lr as Worksheet and Integer respectively.

“lastrow” is the worksheet name.

Here, “B” means Column B because from this column we have started our data range.

Range(“B” & Rows.Count) will select the last row number in this column and then End(xlUp) will go to the last row used in this worksheet, so it will give us the number of the row that we need in our range.

Then the interior color will be changed in Range(“B” & Lr) which means the last used row in column B.

Filling Color in last row

➤Press F5

Result:
Then, you will be able to change the color of the first cell in the last row of this data range.

Filling Color in last row

Method-5: Changing Color for Last Used Column

Previously we have seen the way to change color for the last row, you can also change the Fill color of the first cell in the last used column. Let’s change the cell’s color into the red by using this method.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange5()

Dim Lc As Integer

Dim sht As Worksheet

Dim FirstCell As Range

Set FirstCell = Range("B5")

Set sht = Worksheets("lastcolumn")

Lc = FirstCell.SpecialCells(xlCellTypeLastCell).Column

Cells(5, Lc).Interior.Color = vbRed

End Sub

Here, at first, we declared sht, FirstCell, and Lc as Worksheet, Range, and Long respectively.

“lastcolumn” is the worksheet name.
“B5” is set as FirstCell because it is the starting cell of our data range.
Lc will return the last used column in the worksheet.
Cells(5, Lc) will select the first cell in the last column and here 5 and Lc are the row and column numbers respectively.

Filling Color in last column

➤Press F5

Result:
After that, you will be able to change the color of the first cell in the last column of this data range.

Related Content: Excel OFFSET Dynamic Range Multiple Columns in Effective Way

Method-6: Changing Color for Last Used Row and Column

You can change the color of all of the cells of the last column by combining the above two methods for the last used row and last used column.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange6()

Dim Lc As Integer

Dim Lr As Integer

Dim sht As Worksheet

Set sht = Worksheets("rowandcolumn")

Lr = sht.Range("B" & Rows.Count).End(xlUp).Row

Lc = Range("XFD" & Lr).End(xlToLeft).Column

Range(Cells(5, Lc), Cells(Lr, Lc)).Interior.Color = vbGreen

End Sub

Here, at first, we declared sht, Lr, and Lc as Worksheet, Integer respectively.

“rowandcolumn” is the worksheet name.
Here, “B” means Column B because from this column we have started our data range.

Range(“B” & Rows.Count) will select the last row number in this column and then End(xlUp) will go to the last row used in this worksheet, so it will give us the number of rows that we need in our range.

“XFD” is the last column name and Range(“XFD” & Lr) will select the last column with respect to the last row.

last row and column

Then End(xlToLeft) will go to the last column used in this worksheet, so it will give us the number of the column that we need in our range.
Range(Cells(5, Lc), Cells(Lr, Lc)) is the range between the first cell of the last column and the last cell of the last column in this data range.

 

➤Press F5

Result:
Then, you will be able to change the color of the cells in the last column of this data range.

last row and column

Method-7: Dynamic Range VBA for Static Column 

Sometimes the column numbers for a data range never change, only the row number changes dynamically. To select the data range for this type of data range having a static column you can follow this method.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange7()

Dim sht As Worksheet

Dim LR As Long

Dim FirstCell As Range

Set sht = Worksheets("staticcolumn")

Set FirstCell = Range("B5")

LR = sht.Cells.Find("*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

sht.Range("B5:D" & LR).Select

End Sub

Here, at first, we declared sht, LR, and FirstCell as Worksheet, Long, and Range respectively.
“staticcolumn” is the worksheet name and “B5” is set as FirstCell because it is the starting cell of our data range.
Find will search for any character in the data range and so where the last character will be found it will be the last row.

static column

SearchOrder:=xlByRows searches for the value row by row and returns the position of the string which comes first in the row-wise serial.
Similarly, SearchDirection:=xlPrevious will start the search in the bottom right-hand corner of the data range and search upwards, so it will give the position of the string which comes last.

Range(“B5:D” & LR) is the range of the data table for Column B to D and starts from row 5 to the last used row.

 

➤Press F5

Result:
Afterward, you will be able to select the cells of this data range.

static column

Method-8: Copy Data for Dynamic Range VBA

Here, we will show the way to copy a data range dynamically using VBA code.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange8()

Dim sht As Worksheet

Dim LR As Long

Dim FirstCell As Range

Set sht = Worksheets("copy")

Set FirstCell = Range("B5")

LR = sht.Cells.Find("*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

sht.Range("B5:D" & LR).Copy

End Sub

Here, at first, we declared sht, LR, and FirstCell as Worksheet, Long, and Range respectively.

“copy” is the worksheet name and Range(“B5:D” & LR) is the range of the data table for Column B to D and starts from row 5 to the last used row. Finally, this range will be copied.

copy range

➤Press F5

Result:
After that, you will be able to copy the data range.

copy range

Method-9: Enter Data for Dynamic Range VBA 

Suppose, we want to enter January as a month for each product in the following table. For this purpose, we have added a column named Month. To enter data for a dynamic range you can follow this method.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange9()

Dim Lr As Long

Dim sht As Worksheet

Set sht = Worksheets("enter")

Lr = sht.Cells.Find("*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

Range(Cells(5, 5), Cells(Lr, 5)).Value = "January"

End Sub

Here, at first, we declared sht, LR as Worksheet, Long respectively.
“enter” is the worksheet name.
Range(Cells(5, 5), Cells(Lr, 5)) is the range of the data between the cells E5 and E12 (it will change automatically depending on the last used row) and then the desired value will be entered in this range.

ENTER data

➤Press F5

Result:
Afterward, you will be able to enter the month name January in the Month column.

ENTER data

Method-10: Last Used Cell’s Address for Dynamic Range VBA 

For having the address of the data range in a worksheet you can follow this method.

dynamic range vba

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange10()

Dim Lc As Integer

Dim Lr As Integer

Dim rng As Range

Dim sht As Worksheet

Set sht = Worksheets("range")

Lr = sht.Range("B" & Rows.Count).End(xlUp).Row

Lc = Range("XFD" & Lr).End(xlToLeft).Column

Set rng = Range(Cells(5, 2), Cells(Lr, Lc))

MsgBox "The range is " & rng.Address

End Sub

Here, at first, we declared sht, Lr, Lc, and rng as Worksheet, Integer, and Range respectively.
“range” is the worksheet name.

Range(Cells(5, 2), Cells(Lr, Lc)) is the range between the starting cell B5 and the last cell having the last used row and column number in the range. Then it will be set as rng and finally, it will give the range address.

We used MsgBox to present the output through a message box.

range address

➤Press F5

Result:
In this way, you will be able to get the range address as $B$5:$D$12 for the following data range.

range address

Related Content : Excel Dynamic Named Range Based on Cell Value (5 Easy Ways)

Method-11: Dynamic Range for Tables

You can easily eliminate the Month Column of this following table by using the Table option and a simple VBA code dynamically.

dynamic range vba

Step-01:
➤Go to Insert Tab>>Table Option.

table

Then, the Create Table Dialog Box will open up.
➤Select the data range
➤Click on the Option named My table has headers
➤Press OK.

table

After that, you will get the following data table.

dynamic range vba

➤Go to Table Design Tab>>Properties Option.
➤Write any name in theTable Name Box (Here, I have used Table1).

table

Step-02:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dynrange11()

ActiveWorkbook.Worksheets("Table").ListObjects("Table1").ListColumns("Month").Delete

End Sub

Here, Table is the worksheet name, Table1 is the table name and Month is the column name that you want to delete.

table

➤Press F5

Result:
Afterward, you will be able to delete the column Month.

dynamic range vba

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

table

Conclusion

In this article, I tried to cover the easiest ways to use dynamic range VBA in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo