VBA Named Range | (Static + from Selection + Dynamic)

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

1. Define the workbook where you want to create the named range.
2. Use the names property and then further add method.
3. Specify the name in the “Name” argument.
4. 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 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.