5 Ways to Create A Dynamic Auto-Adjusting VBA Range

5 Ways To Create Dynamic VBA Macro Ranges

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.

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)