Excel VBA Create Named Range: Step-by-Step Guide and 4 Examples to Define Named Ranges with Macros

In this VBA Tutorial, you learn how to create named ranges (for different ranges and with different scopes) with macros.

This VBA Tutorial is accompanied by Excel workbooks containing the macros I use in the examples below. You can get immediate free access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

• General VBA constructs and structures:
  
  
  • Learn about commonly-used VBA terms here.
    
    
  • Learn about working with Sub procedures here.
    
    
  • Learn about the Excel VBA Object Model here.
    
    
  • Learn about working with VBA methods here.
    
    
  • Learn how to identify cell ranges here.
    
    
  • Learn how to declare and work with variables here.
    
    
  • Learn about VBA data types here.
    
    
  • Learn about A1 and R1C1-style references here.
    
    
• Practical VBA applications and macro examples:
  
  
  • Learn how to work with worksheets here.
    
    
  • Learn several ways to find the last row with data here.
    
    
  • Learn several ways to find the last column with data here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Create Named Range

VBA Code to Create Named Range

To create a named range using VBA, use a statement with the following structure:

Scope.Names.Add Name:=RangeName, RefersTo:=NamedRange

Process Followed by VBA to Create Named Range

VBA Statement Explanation

1. Item: Scope.
   
   
   • VBA Construct: Workbook or Worksheet object.
     
     
   • Description: Scope of the named range you create. The scope of a named range can generally be 1 of the following:
     
     
     • Workbook. In this case, Scope must represent a Workbook object. For these purposes, you can use VBA constructs such as the ActiveWorkbook property, the ThisWorkbook property or the Application.Workbooks property.
       
       
     • Worksheet. In this case, Scope must represent a Worksheet object. For these purposes, you can use VBA constructs such as the ActiveSheet property or the Workbook.Worksheets property.
       
       For a more detailed description of how to create a named range with worksheet scope, please refer to the appropriate section below.
       
       
2. Item: Names.
   
   
   • VBA Construct: Workbook.Names property (when Scope is a Workbook object) or Worksheet.Names property (when scope is a Worksheet object).
     
     
   • Description: The Names property returns a Names collection representing all the names in the workbook (when Scope is a Workbook object) or worksheet (when Scope is a Worksheet object) represented by Scope. The Names collection returned by the Workbook.Names property includes worksheet-specific names.
     
     
3. Item: Add.
   
   
   • VBA Construct: Names.Add method.
     
     
   • Description: The Names.Add method sets a new name for a cell range.
     
     
4. Item: Name:=RangeName.
   
   
   • VBA Construct: Name parameter of the Names.Add method.
     
     
   • Description: The Name parameter of the Names.Add method allows you to specify the name of the named range you specify. Consider the following usual requirements when specifying RangeName:
     
     
     • RangeName must start with a letter or underscore.
       
       
     • Certain characters aren't allowed. For example, RangeName can't include spaces.
       
       
     • RangeName can't conflict with an existing name.
       
       
     • RangeName can't be formatted as a cell reference. For example, names such as “ABC1” aren't allowed.
       
       
     • If you want to specify the Name parameter using localized text, (i) work with the NameLocal parameter of the Names.Add method, and (ii) omit the Name parameter of the Names.Add method.
       
       

     If you explicitly declare a variable to represent RangeName, use the String data type.
     
     

5. Item: RefersTo:=NamedRange.
   
   
   • VBA Construct: RefersTo parameter of the Names.Add method.
     
     
   • Description: The RefersTo parameter of the Names.Add method allows you to specify the cell range to which the name refers to. The RefersTo parameter is of the Variant data type. Therefore, you commonly specify NamedRange as one of the following:
     
     
     • A Range object.
       
       
     • A string that uses the A1-style notation.
       
       

     When working with the Names.Add method, you can also specify the cell range to which the name refers to using the following parameters.
     
     

     • RefersToLocal: Allows you to specify the cell range in localized text using the A1-style notation.
       
       
     • RefersToR1C1: Allows you to specify the cell range in R1C1-style notation.
       
       
     • RefersToR1C1Local: Allows you to specify the cell range in localized text using the R1C1-style notation.
       
       

     When working with one of the 4 parameters I refer to (RefersTo, RefersToLocal, RefersToR1C1, or RefersToR1C1Local), specify that single parameter and omit the others.
     
     If you explicitly declare a variable to represent NamedRange, use the Range object data type (if specifying NamedRange as a Range object) or the String data type (if specifying NamedRange as a string).

Macro Example to Create Named Range

The following macro example creates a named range with workbook scope (ThisWorkbook) by setting the name of the cell range composed of cells A5 to C10 (myNamedRange) of the Named Range worksheet (myWorksheet) to “namedRange” (myRangeName).

Sub createNamedRange()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-named-range/

    'declare object variables to hold references to worksheet containing cell range, and cell range itself
    Dim myWorksheet As Worksheet
    Dim myNamedRange As Range

    'declare variable to hold defined name
    Dim myRangeName As String

    'identify worksheet containing cell range, and cell range itself
    Set myWorksheet = ThisWorkbook.Worksheets("Named Range")
    Set myNamedRange = myWorksheet.Range("A5:C10")

    'specify defined name
    myRangeName = "namedRange"

    'create named range with workbook scope. Defined name and cell range are as specified
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRange

End Sub

Effects of Executing Macro Example to Create Named Range

The following image illustrates the results of executing the macro example. The cell range containing cells A5 to C10 is named as “namedRange”.

#2: Create Named Range with Worksheet Scope

VBA Code to Create Named Range with Worksheet Scope

To create a named range with worksheet scope using VBA, use a statement with the following structure:

Worksheet.Names.Add Name:=RangeName, RefersTo:=NamedRangeWorksheet

Process Followed by VBA to Create Named Range with Worksheet Scope

VBA Statement Explanation

1. Item: Worksheet.
   
   
   • VBA Construct: Worksheet object.
     
     
   • Description: You can use VBA constructs such as the ActiveSheet property or the Workbook.Worksheets property to return a Worksheet object representing the worksheet to which you want to limit the scope of the named range.
     
     
2. Item: Names.
   
   
   • VBA Construct: Worksheet.Names property.
     
     
   • Description: The Worksheet.Names property returns a Names collection representing all the names in Worksheet.
     
     
3. Item: Add.
   
   
   • VBA Construct: Names.Add method.
     
     
   • Description: The Names.Add method sets a new name for a cell range.
     
     
4. Item: Name:=RangeName.
   
   
   • VBA Construct: Name parameter of the Names.Add method.
     
     
   • Description: The Name parameter of the Names.Add method allows you to specify the name of the named range you specify. Consider the following usual requirements when specifying RangeName:
     
     
     • RangeName must start with a letter or underscore.
       
       
     • Certain characters aren't allowed. For example, RangeName can't include spaces.
       
       
     • RangeName can't conflict with an existing name.
       
       
     • RangeName can't be formatted as a cell reference. For example, names such as “ABC1” aren't allowed.
       
       
     • If you want to specify the Name parameter using localized text, (i) work with the NameLocal parameter of the Names.Add method, and (ii) omit the Name parameter of the Names.Add method.
       
       

     If you explicitly declare a variable to represent RangeName, use the String data type.
     
     

5. Item: RefersTo:=NamedRangeWorksheet.
   
   
   • VBA Construct: RefersTo parameter of the Names.Add method.
     
     
   • Description: The RefersTo parameter of the Names.Add method allows you to specify the cell range to which the name refers to. The RefersTo parameter is of the Variant data type. Therefore, you commonly specify NamedRangeWorksheet as one of the following:
     
     
     • A Range object.
       
       
     • A string that uses the A1-style notation.
       
       

     When working with the Names.Add method, you can also specify the cell range to which the name refers to using the following parameters.
     
     

     • RefersToLocal: Allows you to specify the cell range in localized text using the A1-style notation.
       
       
     • RefersToR1C1: Allows you to specify the cell range in R1C1-style notation.
       
       
     • RefersToR1C1Local: Allows you to specify the cell range in localized text using the R1C1-style notation.
       
       

     When working with one of the 4 parameters I refer to (RefersTo, RefersToLocal, RefersToR1C1, or RefersToR1C1Local), specify that single parameter and omit the others.
     
     If you explicitly declare a variable to represent NamedRangeWorksheet, use the Range object data type (if specifying NamedRangeWorksheet as a Range object) or the String data type (if specifying NamedRangeWorksheet as a string).

Macro Example to Create Named Range with Worksheet Scope

The following macro example creates a named range with worksheet scope (myWorksheet) by setting the name of the cell range composed of cells D5 to F10 (myNamedRangeWorksheet) of the Named Range worksheet (myWorksheet) to “namedRangeWorksheet” (myRangeName).

Sub createNamedRangeWorksheetScope()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-named-range/

    'declare object variables to hold references to worksheet containing cell range, and cell range itself
    Dim myWorksheet As Worksheet
    Dim myNamedRangeWorksheet As Range

    'declare variable to hold defined name
    Dim myRangeName As String

    'identify worksheet containing cell range, and cell range itself
    Set myWorksheet = ThisWorkbook.Worksheets("Named Range")
    Set myNamedRangeWorksheet = myWorksheet.Range("D5:F10")

    'specify defined name
    myRangeName = "namedRangeWorksheet"

    'create named range with worksheet scope. Defined name and cell range are as specified
    myWorksheet.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeWorksheet

End Sub

Effects of Executing Macro Example to Create Named Range with Worksheet Scope

The following image illustrates the results of executing the macro example. The cell range containing cells D5 to F10 is named as “namedRangeWorksheet”.

#3: Create Named Range from Selection

VBA Code to Create Named Range from Selection

To create a named range from the current selection using VBA, use a statement with the following structure:

Scope.Names.Add Name:=RangeName, RefersTo:=Selection

Process Followed by VBA to Create Named Range from Selection

VBA Statement Explanation from Selection

1. Item: Scope.
   
   
   • VBA Construct: Workbook or Worksheet object.
     
     
   • Description: Scope of the named range you create. The scope of a named range can generally be 1 of the following:
     
     
     • Workbook. In this case, Scope must represent a Workbook object. For these purposes, you can use VBA constructs such as the ActiveWorkbook property, the ThisWorkbook property or the Application.Workbooks property.
       
       
     • Worksheet. In this case, Scope must represent a Worksheet object. For these purposes, you can use VBA constructs such as the ActiveSheet property.
       
       For a more detailed description of how to create a named range with worksheet scope, please refer to the appropriate section above.
       
       
2. Item: Names.
   
   
   • VBA Construct: Workbook.Names property (when Scope is a Workbook object) or Worksheet.Names property (when scope is a Worksheet object).
     
     
   • Description: The Names property returns a Names collection representing all the names in the workbook (when Scope is a Workbook object) or worksheet (when Scope is a Worksheet object) represented by Scope. The Names collection returned by the Workbook.Names property includes worksheet-specific names.
     
     
3. Item: Add.
   
   
   • VBA Construct: Names.Add method.
     
     
   • Description: The Names.Add method sets a new name for a cell range.
     
     
4. Item: Name:=RangeName.
   
   
   • VBA Construct: Name parameter of the Names.Add method.
     
     
   • Description: The Name parameter of the Names.Add method allows you to specify the name of the named range you specify. Consider the following usual requirements when specifying RangeName:
     
     
     • RangeName must start with a letter or underscore.
       
       
     • Certain characters aren't allowed. For example, RangeName can't include spaces.
       
       
     • RangeName can't conflict with an existing name.
       
       
     • RangeName can't be formatted as a cell reference. For example, names such as “ABC1” aren't allowed.
       
       
     • If you want to specify the Name parameter using localized text, (i) work with the NameLocal parameter of the Names.Add method, and (ii) omit the Name parameter of the Names.Add method.
       
       

     If you explicitly declare a variable to represent RangeName, use the String data type.
     
     

5. Item: RefersTo:=Selection.
   
   
   • VBA Construct: RefersTo parameter of the Names.Add method and Selection property.
     
     
   • Description: The RefersTo parameter of the Names.Add method allows you to specify the cell range to which the name refers to.
     
     The Selection property returns a Range object representing the current selected cells. This is the cell range you name.

Macro Example to Create Named Range from Selection

The following macro example creates a named range with workbook scope (ThisWorkbook) by setting the name of the selection (Selection) to “namedRangeFromSelection” (myRangeName).

Sub createNamedRangeFromSelection()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-named-range/

    'declare variable to hold defined name
    Dim myRangeName As String

    'specify defined name
    myRangeName = "namedRangeFromSelection"

    'create named range with workbook scope. Defined name is as specified. Cell range is the selection
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=Selection

End Sub

Effects of Executing Macro Example to Create Named Range from Selection

The following image illustrates the results of executing the macro example. The current selection, containing cells G5 to I10 is named as “namedRangeFromSelection”.

#4: Create Named Range (Dynamic)

VBA Code to Create Named Range (Dynamic)

To create a named range from a dynamic range (where the number of the last row and last column changes) with VBA, use a macro with the following statement structure:

Dim LastRow As Long
Dim LastColumn As Long
Dim NamedRangeDynamic As Range
With Worksheet.Cells
    LastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set NamedRangeDynamic = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
End With
Scope.Names.Add Name:=RangeName, RefersTo:=NamedRangeDynamic

Process Followed by VBA to Create Named Range (Dynamic)

VBA Statement Explanation (Dynamic)

Line #1: Dim LastRow As Long

1. Item: Dim LastRow as Long.
   
   
   • VBA Construct: Dim statement.
     
     
   • Description: Declares the LastRow variable as of the Long data type.
     
     LastRow holds the number of the last row with data in the worksheet containing the cell range you name (Worksheet).

Line #2: Dim LastColumn As Long

1. Item: Dim LastColumn as Long.
   
   
   • VBA Construct: Dim statement.
     
     
   • Description: Declares the LastColumn variable as of the Long data type.
     
     LastColumn holds the number of the last column with data in the worksheet containing the cell range you name (Worksheet).

Line #3: Dim NamedRangeDynamic As Range

1. Item: Dim NamedRangeDynamic As Range.
   
   
   • VBA Construct: Dim statement.
     
     
   • Description: Declares the NamedRangeDynamic variable as of the Range object data type.
     
     NamedRangeDynamic represents the cell range you name.

Lines #4 and #8:With Worksheet.Cells | End With

1. Item: With… End With.
   
   
   • VBA Construct: With… End With statement.
     
     
   • Description: Statements within the With… End With statement are executed on the Range object returned by Worksheet.Cells.
     
     
2. Item: Worksheet.
   
   
   • VBA Construct: Worksheet object.
     
     
   • Description: Represents the worksheet containing the cell range you name. If you explicitly declare an object variable to represent Worksheet, use the Worksheet object data type.
     
     
3. Item: Cells.
   
   
   • VBA Construct: Worksheet.Cells property.
     
     
   • Description: Returns a Range object representing all the cells in Worksheet.

Line #5: LastRow = .Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

1. Item: LastRow.
   
   
   • VBA Construct: Variable of the long data type.
     
     
   • Description: LastRow holds the number of the last row with data in Worksheet.
     
     
2. Item: =.
   
   
   • VBA Construct: Assignment operator.
     
     
   • Description: Assigns the row number returned by the Range.Row property to the LastRow variable.
     
     
3. Item: Find.
   
   
   • VBA Construct: Range.Find method.
     
     
   • Description: Returns a Range object representing the first cell where the information specified by the parameters of the Range.Find method (What, LookIn, LookAt, SearchOrder and SearchDirection) is found. Within this macro structure, this Range object represents the last cell with data in the last row with data in Worksheet.
     
     
4. Item: What:=”*”.
   
   
   • VBA Construct: What parameter of the Range.Find method.
     
     
   • Description: Specifies the data the Range.Find method searches for. The asterisk (*) is a wildcard and, therefore, the Range.Find method searches for any character sequence.
     
     
5. Item: LookIn:=xlFormulas.
   
   
   • VBA Construct: LookIn parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method looks in formulas (xlFormulas).
     
     
6. Item: LookAt:=xlPart.
   
   
   • VBA Construct: LookAt parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method looks at (and matches) a part (xlPart) of the search data.
     
     
7. Item: SearchOrder:=xlByRows.
   
   
   • VBA Construct: SearchOrder parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method searches by rows (xlByRows).
     
     
8. Item: SearchDirection:=xlPrevious.
   
   
   • VBA Construct: SearchDirection parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method searches for the previous match (xlPrevious).
     
     
9. Item: Row.
   
   
   • VBA Construct: Range.Row property.
     
     
   • Description: Returns the row number of the Range object returned by the Range.Find method. Within this macro structure, this row number corresponds to the last row with data in Worksheet.

Line #6: LastColumn = .Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

1. Item: LastColumn.
   
   
   • VBA Construct: Variable of the long data type.
     
     
   • Description: LastColumn holds the number of the last column with data in Worksheet.
     
     
2. Item: =.
   
   
   • VBA Construct: Assignment operator.
     
     
   • Description: Assigns the column number returned by the Range.Column property to the LastColumn variable.
     
     
3. Item: Find.
   
   
   • VBA Construct: Range.Find method.
     
     
   • Description: Returns a Range object representing the first cell where the information specified by the parameters of the Range.Find method (What, LookIn, LookAt, SearchOrder and SearchDirection) is found. Within this macro structure, this Range object represents the last cell with data in the last column with data in Worksheet.
     
     
4. Item: What:=”*”.
   
   
   • VBA Construct: What parameter of the Range.Find method.
     
     
   • Description: Specifies the data the Range.Find method searches for. The asterisk (*) is a wildcard and, therefore, the Range.Find method searches for any character sequence.
     
     
5. Item: LookIn:=xlFormulas.
   
   
   • VBA Construct: LookIn parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method looks in formulas (xlFormulas).
     
     
6. Item: LookAt:=xlPart.
   
   
   • VBA Construct: LookAt parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method looks at (and matches) a part (xlPart) of the search data.
     
     
7. Item: SearchOrder:=xlByColumns.
   
   
   • VBA Construct: SearchOrder parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method searches by columns (xlByColumns).
     
     
8. Item: SearchDirection:=xlPrevious.
   
   
   • VBA Construct: SearchDirection parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method searches for the previous match (xlPrevious).
     
     
9. Item: Column.
   
   
   • VBA Construct: Range.Column property.
     
     
   • Description: Returns the column number of the Range object returned by the Range.Find method. Within this macro structure, this column number corresponds to the last column with data in Worksheet.

Line #7: Set NamedRangeDynamic = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))

1. Item: NamedRangeDynamic.
   
   
   • VBA Construct: Object variable of the Range object data type.
     
     
   • Description: NamedRangeDynamic represents the cell range you name.
     
     
2. Item: =.
   
   
   • VBA Construct: Assignment operator.
     
     
   • Description: Assigns the Range object returned by the Range.Range property to the NamedRangeDynamic object variable.
     
     
3. Item: Range.
   
   
   • VBA Construct: Range.Range property.
     
     
   • Description: Returns a Range object representing the cell range you name. Within this macro structure, the Range property is applied to the Range object returned by the Worksheet.Cells property in the opening statement of the With… End With statement.
     
     
4. Item: Cells(1, 1).
   
   
   • VBA Construct: Cells1 parameter of the Range.Range property, Range.Cells property and Range.Item property.
     
     
   • Description: The Cells1 parameter of the Range.Range property specifies the cell in the upper-left corner of the cell range. Within this macro structure, Cells1 is the Range object returned by the Range.Cells property.
     
     The Range.Cells property returns all the cells within the cell range represented by the Range object returned by the Worksheet.Cells property in the opening statement of the With… End With statement. The Range.Item property is the default property and returns a Range object representing the cell on the first row and first column (Cells(1, 1)) of the cell range it works with.
     
     Since the Worksheet.Cells property in the opening statement of the With… End With statement returns all the cells in Worksheet, this is cell A1 of Worksheet.
     
     
5. Item: Cells(LastRow, LastColumn).
   
   
   • VBA Construct: Cells2 parameter of the Range.Range property, Range.Cells property and Range.Item property.
     
     
   • Description: The Cells2 parameter of the Range.Range property specifies the cells in the lower-right corner of the cell range. Within this macro structure, Cells2 is the Range object returned by the Range.Cells property.
     
     The Range.Cells property returns all the cells within the cell range represented by the Range object returned by the Worksheet.Cells property in the opening statement of the With… End With statement (line #4 above). The Range.Item property is the default property and returns a Range object representing the cell located at the intersection of LastRow and LastColumn.
     
     Since the Worksheet.Cells property in the opening statement of the With… End With statement returns all the cells in Worksheet, this is the cell located at the intersection of the last row and the last column (or the last cell with data) within Worksheet.

Line #9: Scope.Names.Add Name:=RangeName, RefersTo:=NamedRangeDynamic

1. Item: Scope.
   
   
   • VBA Construct: Workbook or Worksheet object.
     
     
   • Description: Scope of the named range you create. The scope of a named range can generally be 1 of the following:
     
     
     • Workbook. In this case, Scope must represent a Workbook object. For these purposes, you can use VBA constructs such as the ActiveWorkbook property, the ThisWorkbook property or the Application.Workbooks property.
       
       
     • Worksheet. In this case, Scope must represent a Worksheet object. For these purposes, you can use VBA constructs such as the ActiveSheet property or the Workbook.Worksheets property.
       
       For a more detailed description of how to create a named range with worksheet scope, please refer to the appropriate section above.
       
       
2. Item: Names.
   
   
   • VBA Construct: Workbook.Names property (when Scope is a Workbook object) or Worksheet.Names property (when scope is a Worksheet object).
     
     
   • Description: The Names property returns a Names collection representing all the names in the workbook (when Scope is a Workbook object) or worksheet (when Scope is a Worksheet object) represented by Scope. The Names collection returned by the Workbook.Names property includes worksheet-specific names.
     
     
3. Item: Add.
   
   
   • VBA Construct: Names.Add method.
     
     
   • Description: The Names.Add method sets a new name for a cell range.
     
     
4. Item: Name:=RangeName.
   
   
   • VBA Construct: Name parameter of the Names.Add method.
     
     
   • Description: The Name parameter of the Names.Add method allows you to specify the name of the named range you specify. Consider the following usual requirements when specifying RangeName:
     
     
     • RangeName must start with a letter or underscore.
       
       
     • Certain characters aren't allowed. For example, RangeName can't include spaces.
       
       
     • RangeName can't conflict with an existing name.
       
       
     • RangeName can't be formatted as a cell reference. For example, names such as “ABC1” aren't allowed.
       
       
     • If you want to specify the Name parameter using localized text, (i) work with the NameLocal parameter of the Names.Add method, and (ii) omit the Name parameter of the Names.Add method.
       
       

     If you explicitly declare a variable to represent RangeName, use the String data type.
     
     

5. Item: RefersTo:=NamedRangeDynamic.
   
   
   • VBA Construct: RefersTo parameter of the Names.Add method.
     
     
   • Description: The RefersTo parameter of the Names.Add method allows you to specify the cell range to which the name refers to.
     
     NamedRangeDynamic is an object variable of the Range object data type representing the cell range you name.

Macro Example to Create Named Range (Dynamic)

The following macro example creates a named range with workbook scope (ThisWorkbook) by dynamically identifying the last row (myLastRow) and last column (myLastColumn) with data in the Named Range worksheet (myWorksheet) and setting its name to “namedRangeDynamic” (myRangeName). In this case, the first cell of namedRangeDynamic is A11 (intersection of myFirstRow and myFirstColumn).

Sub createNamedRangeDynamic()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-named-range/

    'declare object variable to hold reference to worksheet containing cell range
    Dim myWorksheet As Worksheet

    'declare variables to hold row and column numbers that define named cell range (dynamic)
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long

    'declare object variable to hold reference to cell range
    Dim myNamedRangeDynamic As Range

    'declare variable to hold defined name
    Dim myRangeName As String

    'identify worksheet containing cell range
    Set myWorksheet = ThisWorkbook.Worksheets("Named Range")

    'identify first row and first column of cell range
    myFirstRow = 11
    myFirstColumn = 1

    'specify defined name
    myRangeName = "namedRangeDynamic"

    With myWorksheet.Cells

        'find last row and last column of source data cell range
        myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

        'specify cell range
        Set myNamedRangeDynamic = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))

End With

    'create named range with workbook scope. Defined name is as specified. Cell range is as identified, with the last row and column being dynamically determined
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeDynamic

End Sub

Effects of Executing Macro Example to Create Named Range (Dynamic)

The following image illustrates the results of executing the macro example. The cell range containing data (Data) (cells A11 to I20) is named as “namedRangeDynamic”.

References to VBA Constructs Used in this VBA Tutorial

Use the following links to visit the appropriate webpage in the Microsoft Developer Network:

1. Specify the scope of a named range (workbook or worksheet):
   
   
   • Workbook object.
     
     
   • Workbook.Names property.
     
     
   • Application.ActiveWorkbook property.
     
     
   • Application.ThisWorkbook property.
     
     
   • Application.Workbooks property.
     
     
   • Worksheet object.
     
     
   • Worksheet.Names property.
     
     
   • Application.ActiveSheet property.
     
     
   • Workbook.Worksheets property.
     
     
2. Identify the cell range to name:
   
   
   • Range object.
     
     
   • Worksheet.Range property.
     
     
   • Worksheet.Cells property.
     
     
   • Application.Selection property.
     
     
   • Range.Range property.
     
     
   • Range.Cells property.
     
     
   • Range.Item property.
     
     
   • Identify the last row or column with data in a worksheet:
     
     
     • Range.Find method.
       
       
     • Range.Row property.
       
       
     • Range.Column property.
       
       
3. Set a new named range:
   
   
   • Names collection.
     
     
   • Name object.
     
     
   • Names.Add method.
     
     
   • Range.Name property.
     
     
4. Work with variables and data types:
   
   
   • Dim statement.
     
     
   • Set statement.
     
     
   • Data types:
     
     
     • String data type.
       
       
     • Long data type.