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.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
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 to 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.
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel