Home > Macros > Basics > Named Ranges
Instead 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
Set up the workbook
Create the Dynamic Range for number of rows
Create a Dynamic Range for YearMth column
Create a Dynamic Range for Region column
Create Dynamic Ranges with a macro
Use Dynamic Ranges in formulas
Use Dynamic Ranges in pivot tables
Download the sample workbook
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.
- In a new Workbook, rename Sheet 1 as Data
- Rename Sheet2 as Sumproduct Report
- Rename Sheet3 as Pivot Table Report
- On the Data sheet in cells A1:G1, add the headings YearMth, Region, Employee, Item, Units, Price, Unit Cost and Total.
- 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
- Choose Insert | Name | Define
- Type a name for the first range -- lrow
- 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.
- Choose Insert | Name | Define
- Type a name for the first range --YearMth
- 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) - 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.
- Choose Insert | Name | Define
- Type a name for the range -- Region
- 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) - 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.
- Select a cell in the database
- Choose Data | PivotTable and PivotChart Report
- Select 'Microsoft Excel List or Database', click Next.
- For the range, type myData , then click Next
- Click the Layout button
- 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
- 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
FAQs
How to set dynamic range in macro? ›
- Select a cell in the database.
- Choose Data | PivotTable and PivotChart Report.
- Select 'Microsoft Excel List or Database', click Next.
- For the range, type myData , then click Next.
- Click the Layout button.
- Drag field buttons to the row, column and data areas. ...
- Click OK, then click Finish.
- Sub LastUsedRow() 'Excel VBA for Last cell with data in Column A. Dim lw As Integer. lw = Range("A1048576").End(xlUp).Row. ...
- Sub LastUsedRow2() 'Excel VBA for Last cell with data in Column A. Dim lw As Integer. lw=Range("A" & Rows.Count).End(xlUp).Row. ...
- Sub LastUsedRow3() Dim lw As Integer.
To set the reference, we need to use the “SET” keyword and enter the cell addresses using the RANGE object. The variable “Rng” refers to the cells A1 to B5. Instead of writing the cell address Range (“A1:B5”), we can use the variable name “Rng.” It will insert the mentioned value from the A1 to the B5 cells.
How do you automate a range in VBA? ›- Method-1: Select Cells Containing Values Through UsedRange Property.
- Method-2: Choose Dynamic Range Including Headers with CurrentRegion Property.
- Method-3: SpecialCells for Selecting Dynamic Range Excluding Headers.
- Method-4: Change Color for Last Used Row with Excel VBA.
- Consider the genre and listener. Before pushing faders and slapping compressors on every track, make note of the style of music you're mixing and where it will be listened to. ...
- Don't forget proper gain staging. ...
- Listen for too many dynamics. ...
- Listen for over-compression. ...
- Use a meter to monitor dynamic range.
Dynamic Formulas begin with &== and are followed by an Excel formula. Repeating Dynamic Formulas begin with &=&= and are followed by an Excel formula. You may use most of Excel's functions in a Dynamic Formula.
How do I create a dynamic power automate in Excel? ›In Power Automate, select the Manually triggered Flow then click on the Next step. Now to create a table in an excel file, select Create table -Excel Online Business action. Then Provide the Location, and Document library where you have saved the file. Choose the File from the Folder.
How do you write a range in a macro? ›The basic syntax of the VBA range property consists of the keyword “Range” followed by the parentheses. The relevant range is included within double quotation marks. For example, the following reference refers to cell C1 in the given worksheet and workbook.
How do I create a named range in macro? ›- Select the cells you want to name, then click Insert > Name > Define. In the Name box, type a name for the range, then click OK.
- Click the cell where you want the upper-left corner of the range to appear, then click Insert > Name > Create.
- Choose Options from the Tools menu. Excel displays the Options dialog box.
- Make sure the Edit tab is selected.
- Clear the Ask To Update Automatic Links check box.
- Click on OK.
How do I get the active cell range in VBA? ›
- Type the keyword “ActiveCell”.
- Type a dot (.) to get the list properties and methods.
- Select the property or method that you want to use.
- Run the code to perform the activity to the active cell.
“Set”, is a keyword used in VBA programming to assign a reference to an object or cell range which is going to remain fix throughout the program or code in Excel.
How do I randomize a range in Excel VBA? ›To generate a random number, in VBA, there is a function called RND. This function stands for random and when you use this function in code it returns a random number between 0 to 1. In RND, you don't need to specify any argument.
Which dynamic range is best? ›For most tracks, it's best to shoot for a Loudness Range of -4 to -8 LU and a Dynamic Range above 6DR. This ensures that your mix will be loud enough to hear clearly on any system and dynamic enough to prevent unwanted audio distortion. However, these ranges can vary depending on the genre.
What is the best dynamic range to use? ›A good dynamic range depends on what you intend to photograph. If landscapes and outdoor photography are your jam, then a good dynamic range should be at the top of your priority list. High-quality digital bodies have a range between 12 and 15 stops at base ISO levels – but some of these are incredibly expensive.
What are the two types of dynamic range? ›The 2 Types of Dynamic Range
One is the dynamic range of the subject, the second is the dynamic range of your camera. The dynamic range of the subject is a measure of the range of light intensities from the shadows to the highlights.
Selecting a Single Cell Using VBA
Range(“A1”) tells VBA the address of the cell that we want to refer to. Select is a method of the Range object and selects the cells/range specified in the Range object. The cell references need to be enclosed in double quotes.
In the VBA editor go to the View menu and click on Locals Window. Each time F8 is pressed one line of VBA is executed. When the range MyRange is set, you can see that values appear for it in the Locals window. By examining the MyRange object you can drill down to see the values in it.
How does used range work in VBA? ›The UsedRange in VBA is a worksheet property that returns a range object representing the range used (all Excel cells used or filled in a worksheet) on a particular worksheet. It is a property representing the area covered or bounded by the top-left used cell and the last right-used cells in a worksheet.
How do I create a dynamic data table in Excel? ›One way to create dynamic tables and charts in Excel is to use formulas that can define a range of cells based on a condition or a reference. For example, you can use the OFFSET and COUNTA functions to create a named range that adjusts to the number of non-empty cells in a column.
What is an example of dynamic range? ›
A high-contrast image is one with both dark shadows and bright highlights. The dynamic range of such an image can exceed your camera's ability to capture it, which is why a photo of a person standing in front of a light-filled window fails to catch the details of the person's face as well as the scene outside.
What is dynamic formula in Excel? ›Excel formulas that return a set of values, also known as an array, return these values to neighboring cells. This behavior is called spilling. Formulas that can return arrays of variable size are called dynamic array formulas.
How do you create a range based on a cell value in Excel? ›Define range based on cell value
To do calculation for a range based on another cell value, you can use a simple formula. Select a blank cell which you will put out the result, enter this formula =AVERAGE(A1:INDIRECT(CONCATENATE("A",B2))), and press Enter key to get the result.
Add dynamic content Power Automate
Go to +Create > Instant cloud flow > Manually trigger the flow > + Add an input > Number. Next, we will add a compose action with an expression using add function. Go to + New step > Compose. This is how to add dynamic content in Power Automate.
- Zapier.
- dataloader.io.
- IBM App Connect.
- Bedrock Data.
- Flowgear Integration Platform.
- cloudHQ.
- Oracle Self-Service Integration.
- Simplr.
- Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. ...
- Drag the fill handle .
- If needed, click Auto Fill Options. and choose the option you want.
- Range("A84:B", "D84:E", "H84:J" & LastRow). Select.
- Range("A84:B,D84:E,H84:J" & LastRow). Select.
- Range("A84:B & LastRow,D84:E & LastRow,H84:J & LastRow"). Select.
- On the Tools tab, select Visual Basic to open the Visual Basic Editor.
- In the Visual Basic Project Explorer, right click on the project folder, and choose Insert > Module.
- In the code window, add a subroutine by entering Sub followed by the name for the macro.
Excel VBA Subscript Out of Range. Subscript out of range is an error we encounter in VBA when we try to reference something or a variable that does not exist in a code. For example, suppose we do not have a variable named x. Then, if we use the MsgBox function on x, we will encounter a “Subscript out of range” error.
How do I create a named range in Excel fast? ›- Select the range you want to name, including the row or column labels.
- Click Formulas > Create from Selection.
- In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. ...
- Click OK.
What is the rule for macro naming? ›
Use only alphanumeric characters and the underscore character ( _ ). Spaces and other symbols are not allowed. Use fewer than 255 characters. Avoid names that match Visual Basic or InfoConnect commands.
How do I automatically update data from one Excel sheet to another? ›- Open your worksheets. First, open both spreadsheets that you want to connect. ...
- Select data from your source worksheet. ...
- Copy the formula. ...
- Click the corresponding area of the new sheet. ...
- Enter the link. ...
- Add criteria. ...
- Set update intervals. ...
- Save the workbook.
Configure a macro to run automatically upon opening a workbook. Create a Workbook_Open event. The following example uses the Open event to run a macro when you open the workbook. Open the workbook where you want to add the macro, or create a new workbook.
Why is my macro not updating automatically? ›Excel formulas not updating
When Excel formulas are not updating automatically, most likely it's because the Calculation setting has been changed to Manual instead of Automatic. To fix this, just set the Calculation option to Automatic again.
All you need to do is decrease the Lights sliders in order to darken the overexposed sky. Then, drag the Shadows slider to the right to brighten up the shadows. This will lighten the overly dark areas. The overall exposure is then more balanced and we have improved dynamic range.
How do I select a range in VBA? ›To select the cell in the different worksheets, specify the worksheet by its name. To specify the worksheet, we need to use the “WORKSHEET” object and enter the worksheet name in double quotes. For example, if you want to select cell A1 in the worksheet “Data Sheet,” specify the worksheet just like below.
How is dynamic range calculated? ›Dynamic Range (DR) is given by: DR = Full well capacity + Detection limit.
How many stops of dynamic range is good? ›On average, a high-quality digital camera captures between 12 and 15 stops of dynamic range at the base level ISO, which is typically around 100. However, perfect lighting conditions must exist for measuring the capacity – no dark shadows and no highlights.
What does dynamic range depend on? ›Dynamic range is the difference between the brightest and darkest parts of an image. The range of dynamic range depends on what kind of sensor you are using and how your camera is set up. For example, a point-and-shoot camera has much less dynamic range than a DSLR or mirrorless system.
How do I create a dynamic formula in Excel? ›Dynamic Formulas begin with &== and are followed by an Excel formula. Repeating Dynamic Formulas begin with &=&= and are followed by an Excel formula. You may use most of Excel's functions in a Dynamic Formula.
How do I automatically update named ranges in Excel? ›
- On the Ribbon, click the Formulas tab.
- Click Name Manager.
- In the list, click on the name that you want to change.
- In the Refers To box, change the range reference, or drag on the worksheet, to select the new range.
- Click the check mark, to save the change.
- Click Close, to close the Name Manager TOP.
- Press Ctrl+F3. ...
- Click New. ...
- In the Name field, enter the following as a name: Print_Area.
- Using the Scope drop-down list, choose the worksheet that contains the cell (C7) that contains the print range.
- In the Refers To field, enter the following formula: =INDIRECT(Sheet1!$C$7)
- Click OK.
- Method 1: Copy Dynamic Range to Another Workbook with WorkSheet Objects.
- Method 2: Copy Dynamic Range to Another Workbook Adding New Sheet.
- Method 3: Copy Dynamic Range to a New Workbook.
To select a range just start with Range and then input the desired range of cells to select or work on. If you want to select the range A1:A5, input Range(A1:A5). Select and thats it. Remember that this will select the range on the currently active or visible worksheet in Excel.
How do I select an active range in Excel VBA? ›Selecting a Single Cell Using VBA
Range(“A1”) tells VBA the address of the cell that we want to refer to. Select is a method of the Range object and selects the cells/range specified in the Range object. The cell references need to be enclosed in double quotes.