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 11 easy ways of using a dynamic range in VBA to save time and work effectively.
Here, we have changed the color of all of the dynamic range cells of the last column with the help of VBA. Follow the rest of the part to learn the process along with other suitable methods in detail.
Dynamic Range VBA: 11 Suitable Ways to Use
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 version according to your convenience.
Method-1: Select Cells Containing Values Through 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. Hence, follow the steps below.
Steps:
- Go to Developer Tab>>Visual Basic Option.
- Then, the Visual Basic Editor will open up.
- Go to Insert Tab >> Module Option.
- Write the following code.
Sub dynrange1()
Worksheets("usedrange").UsedRange.Select
End Sub
Note:
Here, “usedrange” is the sheet name. UsedRange property will select all the cells in this worksheet that have values in them.
- Press F5.
- In this way, you will select the whole data range including the heading.
Method-2: Choose Dynamic Range Including Headers with CurrentRegion Property
If you want to select the data range including headers, then you can use the CurrentRegion property like this method. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- 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.
- Press F5.
- In this way, you will select the whole data range including headers.
Method-3: SpecialCells for Selecting Dynamic Range Excluding Headers
If you want to select only the data range excluding headers, then you can use the SpecialCells property in your VBA code. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- 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.
- Press F5.
- Afterwards, you will be able to select the whole data range excluding headers.
Method-4: Change Color for Last Used Row with Excel VBA
Here, we will change the Fill color of the first cell in the last used row into red by using this method. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- Here, at first, we declared sht, Lr as Worksheet and Integer respectively.
- “lastrow” is the worksheet name.
- Again, “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.
- Press F5.
- Then, you will be able to change the color of the first cell in the last row of this data range.
Read More: How to Use Dynamic Range for Last Row with VBA in Excel
Method-5: Edit Color of 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. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- 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.
- Press F5.
- After that, you will be able to change the color of the first cell in the last column of this data range.
Read More: Dynamic Range for Multiple Columns with Excel OFFSET
Method-6: Changing Color for Last Used Row and Column
You can change the color of all the cells of the last column by combining the above two methods for the last used row and last used column. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- 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.
- 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.
- Then, you will be able to change the color of all the cells in the last column of this data range.
Read More: How to Create a Range of Numbers in Excel
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. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- 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.
- 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.
- Afterward, you will be able to select the cells of this data range.
Method-8: Copy Data for Dynamic Range with VBA
Here, we will show the way to copy a data range dynamically using VBA code. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- 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.
- Press F5.
- After that, you will be able to copy the data range.
Read More: Data Validation Drop Down List with Excel Table Dynamic Range
Method-9: Enter Data for Dynamic Cell Range
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. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- 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.
- Press F5.
- Afterward, you will be able to enter the month name January in the Month column.
Read More: Excel VBA: Dynamic Range Based on Cell Value
Method-10: Get Last Used Dynamic Cell’s Address
For having the address of the data range in a worksheet you can follow this method. Hence, follow the steps below.
Steps:
- Follow the Steps 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
Note:
- 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.
- Press F5.
- In this way, you will be able to get the range address as $B$5:$D$12 for the following data range.
Read More: Dynamic Named Range Based on Cell Value in Excel
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. Hence, follow the steps below.
Steps:
- Go to Insert Tab>>Table Option.
- Then, the Create Table Dialog Box will open up.
- Select the data range.
- Click on the Option named My table has headers.
- Press OK.
- After that, you will get the following data table.
- Go to Table Design Tab>>Properties Option.
- Write any name in the Table Name Box (Here, I have used Table1).
- Follow the Steps of Method-1.
- Type the following code.
Sub dynrange11()
ActiveWorkbook.Worksheets("Table").ListObjects("Table1").ListColumns("Month").Delete
End Sub
Note:
Here, Table is the worksheet name, Table1 is the table name and Month is the column name that you want to delete.
- Press F5.
- Afterward, you will be able to delete the column Month.
Download Workbook
You can download the workbook used for the demonstration from the download link below.
Conclusion
In this article, I tried to cover the easiest ways to use dynamic range in VBA in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.
