5 Ways to Create A Dynamic Auto-Adjusting VBA Range
Dynamic Code Is Vital!
A huge turning point for me when I was teaching myself how to write VBA was being able to create dynamic code. This meant I didn’t have to go into my macros and change range addresses in my code every time the size of my data changed (which was typically every day). Through lots of trial and error, I’ve come up with a list of 5 different methods you can use to turn your static range references into powerful, auto-adjusting machines! All of these code snippets have advantages and disadvantages, so make sure you pick the one that works for your particular situation.
Method 1: UsedRange
The UsedRange method creates a range that includes every cell that contains data in it on the spreadsheet. It is vital that you refresh (recalculate) the UsedRange before using it as the method will pick up ghost cells (cells that had values in them and you used the Delete key to remove the values). Manually you can do this by saving your spreadsheet or you can use the command shown in the below code to refresh with VBA.
Sub DynamicRange() 'Best used when only your target data is on the worksheet 'Refresh UsedRange (get rid of "Ghost" cells) Worksheets("Sheet1").UsedRange 'Select UsedRange Worksheets("Sheet1").UsedRange.Select End Sub
Method 2: Ctrl + Shift + Right Arrow/Down Arrow
This code simulates the range that would show up if you were to use the keyboard shortcut Ctrl + Shift + Right Arrow and then Ctrl + Shift + Down Arrow. If the last cell in the first row or the last cell in the first column are empty, this code will not calculate properly.
Sub DynamicRange() 'Best used when first column has value on last row and first row has a value in the last column Dim sht As Worksheet Dim LastRow As Long Dim LastColumn As Long Dim StartCell As Range Set sht = Worksheets("Sheet1") Set StartCell = Range("D9") 'Find Last Row and Column LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column 'Select Range sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select End Sub
Method 3: SpecialCells – LastCell
This code uses the SpecialCells method (the non-VBA terminology is called Go To Cells) to search for the last cell containing data on the spreadsheet. Once this cell is found, the code can use the cell reference of the “last cell” to determine the last row and column.
Sub DynamicRange() 'Best used when you want to include all data stored on the spreadsheet Dim sht As Worksheet Dim LastRow As Long Dim LastColumn As Long Dim StartCell As Range Set sht = Worksheets("Sheet1") Set StartCell = Range("D9") 'Refresh UsedRange Worksheets("Sheet1").UsedRange 'Find Last Row and Column LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column 'Select Range sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select End Sub
Method 4: CurrentRegion
This example uses the CurrentRegion method. CurrentRegion tries to calculate the range associated with a cell reference by analyzing the surrounding cells. If there is a completely blank row or blank column, CurrentRegion stops looking and assumes that you were only wanting the data that was connected with your starting point. Make sure there are no chances of your data having a completely blank row or column before using this method.
Sub DynamicRange() 'Best used when your data does not have any entirely blank rows or columns Dim sht As Worksheet Dim StartCell As Range Set sht = Worksheets("Sheet1") Set StartCell = Range("D9") 'Select Range StartCell.CurrentRegion.Select End Sub
Method 5: Static Columns
I’ve experienced lots of situations where the column length never changed and the VBA code just needed to dynamically adjust for row length. In cases like this, you may not want to write lines to look for the last column. The below code shows you how to adjust a ranges row length dynamically.
Note: You could also use the LastRow calculation used in Method 2 instead of the Find calculation shown below.
Sub DynamicRange() 'Best used when column length is static Dim sht As Worksheet Dim LastRow As Long Dim LastColumn As Long Dim StartCell As Range Set sht = Worksheets("Sheet1") Set StartCell = Range("D9") 'Refresh UsedRange Worksheets("Sheet1").UsedRange 'Find Last Row LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Select Range sht.Range("D9:M" & LastRow).Select End Sub
What Can I Do With A Dynamic Range?
There are a ton of scenarios that may require you to have an automatically expanding and collapsing range reference. Examples could be:
- Resizing a Pivot Table source range
- Looping through cells in a data set
- Deleting only the raw data range
There are many, many more examples of this and I’m am sure you can think of a few examples yourself. Let me know in the comments section below how you use resizing a range in your macro code! Also, if you can think of any other ways to use VBA code to resize your ranges automatically, post your coding method in the comments section so we can improve the current list. I look forward to reading about your experiences.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
VBA Code To Find Last Row Or Last Column (Best Way)
Dynamic VBA Code With Last Row/Column Finding the Last Row or Last Column within your VBA code is key to...
VBA To Find All Cells That Are Blank/Empty
What This VBA Code Does I’ve had numerous instances over the years when I have needed to target blank or...
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.