Home > Macros > Basics > Named Ranges Create Dynamic Ranges With MacroInstead of using OFFSET to create a dynamic range, you can use the non-volatile INDEX function. Thanks to Roger Govier, who created this tutorial and sample file |
Instead of using OFFSET to create a dynamic range, you can use the non-volatile INDEX function. Then, use the dynamic range names to
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.
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
Next, you'll create a dynamic range for the YearMth column.
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.
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 Sub CreateNames_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure CreateNames of Module Technology4U" End Sub
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.
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.
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.
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 zipped CreateNames sample file
Last updated: January 22, 2023 3:42 PM