How to Create Dynamic Ranges in Excel With Macro (2024)

Set up the workbook

Instead of using OFFSET to create a dynamic range, you can use the non-volatile INDEX function. Then, use the dynamic range names to

• create a PIVOT TABLE
• show totals with SUMPRODUCT

In the sample file there's a macro that will create the range names automatically for you.

You can download and use the sample file, or create your own file to use, as described here.

1. In a new Workbook, rename Sheet 1 as Data
2. Rename Sheet2 as Sumproduct Report
3. Rename Sheet3 as Pivot Table Report
4. On the Data sheet in cells A1:G1, add the headings YearMth, Region, Employee, Item, Units, Price, Unit Cost and Total.
5. Enter sample data in each column or use the data in the zipped sample Excel Names file.

   

Create Dynamic Range for number of rows

Because we are going to need all ranges to be of equal length, and to save having to use the COUNTA function over and over, it is easier to create a dynamic range for the number of rows, which we will give the shortened name lrow

1. Choose Insert | Name | Define
2. Type a name for the first range -- lrow
3. In the Refers To box, enter an Index formula that defines the range size, based on the count of numbers in column A, YearMth:
   = COUNTA(Data!$A:$A)

Create Dynamic Range for YearMth

Next, you'll create a dynamic range for the YearMth column.

1. Choose Insert | Name | Define
2. Type a name for the first range --YearMth
3. In the Refers To box, enter an Index formula that defines the range size, based on the count of numbers in the Date column:
   =$A$2:INDEX(Data!$A:$A,lrow)
4. The arguments used in this Index function are:

• Number of Rows: lrow as previously defined

Create Dynamic Range for Region

You could follow the same steps to create dynamic ranges for the remaining columns in the table, changing the column reference in the Refers To formula. We'll manually create one more dynamic range -- for the Region, which is in column B.

1. Choose Insert | Name | Define
2. Type a name for the range -- Region
3. In the Refers To box, enter an Index formula that defines the range size, based on the count of numbers in the Region column:
   =$B$2:INDEX(Data!$B:$B,lrow)
4. Click the Add button

Create Dynamic Ranges with Macro

You could repeat the steps for all the remaining columns, but that would be a long, repetitive process. To make things easier, and much faster, you can use a macro to automate the naming.

This only requires a small amount of VBA code, as shown below. This code is also in the sample file that you can download.

In addition to lrow, the VBA code creates a name lcol to hold the last Column number used on the sheet. It also adds a Dynamic Range called MyData which covers the whole data set and is then used when producing a Pivot Table.

Sub CreateNames()'https://www.contextures.com/xlNames03.html' written by Roger Govier, Technology4U Dim wb As Workbook, ws As Worksheet Dim lrow As Long, lcol As Long, i As Long Dim myName As String, Start As String ' set the row number where headings are held as a constant ' change this to the row number required if not row 1 Const Rowno = 1 ' set the Offset as the number of rows below Rowno, where the ' data begins Const Offset = 1 ' set the starting column for the data, in this case 1 ' change if the data does not start in column A Const Colno = 1 On Error GoTo CreateNames_Error Set wb = ActiveWorkbook Set ws = ActiveSheet ' count the number of columns used in the row designated to ' have the header names lcol = ws.Cells(Rowno, 1).End(xlToRight).Column lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row Start = Cells(Rowno, Colno).Address wb.Names.Add Name:="lcol", _ RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")" wb.Names.Add Name:="lrow", _ RefersToR1C1:="=COUNTA(C" & Colno & ")" wb.Names.Add Name:="myData", RefersTo:= _ "=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)" For i = Colno To lcol ' if a column header contains spaces, ' replace the space with an underscore ' spaces are not allowed in range names. myName = Replace(Cells(Rowno, i).Value, " ", "_") If myName = "" Then ' if column header is blank, warn the user and ' stop the macro at that point ' names will only be created for those cells with text in them. MsgBox "Missing Name in column " & i & vbCrLf _ & "Please Enter a Name and run macro again" Exit Sub End If wb.Names.Add Name:=myName, RefersToR1C1:= _ "=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"nexti: Next i On Error GoTo 0 MsgBox "All dynamic Named ranges have been created" Exit SubCreateNames_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure CreateNames of Module Technology4U"End Sub

Use Dynamic Ranges in Formulas

Examples are shown in the sample file of how to use Sumproduct formulas with Range Names.

For example, instead of using range references in a SUMPRODUCT formula, you can use the Region name and the Total name.

Use Dynamic Ranges in Pivot Tables

Examples are shown in the sample file of how to create pivot tables using Range Names.

For example, the macro creates a Dynamic Range called myData, with a formula of :

=Data!$A$1:INDEX(Data!$1:$65535,lrow,lcol)

Then setting up a Pivot table report is very easy -- just use myData as the range in Step 2 of the PivotTable and PivotChart Wizard.

1. Select a cell in the database
2. Choose Data | PivotTable and PivotChart Report
3. Select 'Microsoft Excel List or Database', click Next.
4. For the range, type myData , then click Next
5. Click the Layout button
6. Drag field buttons to the row, column and data areas. For example:
   • Drag YearMth to the Page area
   • Drag Region to the Row area
   • Drag Item to the Column area
   • Drag Total to the Data area
7. Click OK, then click Finish

And all the data is summarized, with no complex formulas required. Also, you can quickly rearrange the pivot table, by dragging the fields to a different location. For example, you could drag Item to the Row area, beside the Region field, for a different layout.

Update the Pivot Table

After you add or change the data on the Data sheet, just right click anywhere on the Pivot Table and choose Refresh Data. All the values will be automatically recalculated.

Download the Workbook

Download the zipped CreateNames sample file