To create a named range using VBA, you need to use the “Names” property further with the “Add” method. In add method, you have arguments to define the name that you wish to give to the range and specify the address of the range (make sure to use the dollar sign with the address to freeze the range).
Create a Name Range using VBA
- Define the workbook where you want to create the named range.
- Use the names property and then further add method.
- Specify the name in the “Name” argument.
- Refer to the range using the “ReferTo” argument.
In the above example, you have the active workbook, and then by using the “Names” property with the “Add” method you have defined the name of the range, and in the end, the address of the range that you want to use.
As I said earlier, in the range address, you need to use the $ sign to freeze the address. You can also use ThisWorkbook to refer to the workbook where you are writing the code, or you can use refer to a different workbook using the workbook object.
VBA to Create Named Range from Selection
You can also use the selection property to create a named range from the selection. Consider the following code.
ActiveSheet.Names.Add Name:="myRangeName", RefersTo:=Selection
And in the following code, you have a message box with which you can enter the name that you want to give to the named range.
Sub vba_named_range()
Dim iName As String
iName = InputBox("Enter Name for the Selection.")
ActiveSheet.Names.Add Name:=iName, RefersTo:=Selection
End Sub
Resizing a Named Range using VBA (Dynamic Named Range)
To resize a named range already there in the worksheet, you need to use the resize property and tell VBA how many rows and columns you want to expand from the current range. Consider the following code which expands the named range “myRange” which has cell A1 as range initially but resizes it to column M and row 11.
Sub vba_named_range()
Dim iRow As Long
Dim iColumn As Long
iRow = ActiveSheet.Range("A1").End(xlDown).Row
iColumn = ActiveSheet.Range("A1").End(xlToRight).Column
ActiveSheet.Range("myRange") _
.Resize(iRow, iColumn).Name = "myRange"
End Sub
I have split this into three parts to make you understand this, now, let’s get into this.
- In the FIRST part, you have variables declared to store rows and column count.
- In the SECOND part, you have used the “END” method with the range to get the last row and column and store it for the variables.
- In the THIRD part, you have used the Resize property with the named range “myRange”. And after that, the row and column number that you have in the variables.
When you run this code, it resizes the old range according to the data you have and makes it a dynamic named range. Whenever you need to update it, you can run the code and resize the existing named range.
Related Tutorials
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells