|
|
Search
Contextures Sites
|
||
Excel Names -- Create Dynamic Ranges With a Macro
|
Thanks to Roger Govier, who created this tutorial and sample file. As an alternative to using OFFSET for creating a dynamic formula to define a range, you can use the non-volatile INDEX function. In this example the dynamic range names are used to create a PIVOT TABLE, and to show a similar type of report constructed using SUMPRODUCT. In the sample file there's a macro that will create the range names automatically for you. Set up the workbook Download the zipped sample Excel Names file |
|
Set up the workbookYou can download and use the sample Excel Names file, or create your own file to use, as described here.
|
|
Create Dynamic Ranges with a macroYou 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 Excel Names 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()
'http://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
Download the zipped sample Excel Names file |
|
Use Dynamic Ranges in FormulasExamples are shown in the sample Excel Names 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 TablesExamples are shown in the sample Excel Names file of how to create pivot tables using Range Names. For example, the macro creates a Dynamic Range called myData, with a formula of :
|
|
|
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.
|
|
Update the Pivot TableAfter 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. |
|
