Alok Paul
A Named Range is to set a name for one or more cells. We can call named range instead of a cell or range. In this article, we will discuss how to create a named range from the selection using Excel VBA.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 VBA Examples to Create Named Range from Selection in Excel
We will show how to create a named range from selection in Excel. We will apply the Named Range to the following dataset.
1. A Simple VBA Code to Create a Named Range from Selection in Excel
We will create a named range from the selection using Excel VBA.
Step 1:
- Go to the Sheet Name field at the bottom of any sheet.
- A menu will appear. Choose View Code from the menu.
The VBA command module will appear. We will write the VBA code on this module, then run that code.
Step 2:
- First, select the range where to create the named range. The name bar is not showing any name.
- Then, copy and paste the following VBA code on the module.
Sub Create_Named_Range_1()
 Dim range_1 As Range
 Set range_1 = Selection
 Names.Add "SalarySheet_1", range_1
End Sub
Step 3:
- Press F5 to run the code.
We can see that name of the range is showing now.
Read More: How to Set Value to a Named Range with Excel VBA (3 Methods)
2. Put Cell Reference Directly in VBA Code to Create Named Range
We can use the cell reference directly on the VBA code to create a named range.
Step 1:
- Press Alt+F11 to enter the VBA command module.
- Copy and paste the following VBA code into the module.
Sub Create_Named_Range_2()
Dim range_1 As Range
Dim name_range As String
Set range_1 = Range("B5:C9")
name_range = "SalarySheet_2"
Names.Add name_range, range_1
End Sub
Step 2:
- Hit the F5 button to run the code.
We can see the name of the range here.
Read More: How to Name a Group of Cells in Excel (3 Methods +1 Bonus)
3. VBA Code to Create Named Range from User’s Input
We can take input from the user to create a Named Range using Excel VBA.
Step 1:
- Hit Alt+F11 to enter the command module.
- Write the following VBA code on the command module.
Sub Create_Named_Range_3()
Dim range_1 As Range
Dim name_range As String
Set range_1 = Application.InputBox("Range", "Select Range", Type:=8)
name_range = InputBox("Enter Name for the Selection")
Names.Add name_range, range_1
End Sub
Step 2:
- Press F5 to run the code.
- A window will appear to choose the range. Select the range from the dataset.
Step 3:
- Then, click OK.
- A new window will appear put the name of the range.
Step 4:
- Again, click OK.
The name is shown here.
Read More: How to Edit Name Box in Excel (Edit, Change Range and Delete)
4. Create a Named Range of Column Using Excel VBA
We can name a range of a single column using Excel VBA.
Step 1:
- Press Alt+F11 to enter the command module.
- Copy and paste the VBA code below.
Sub Create_Named_Range_4()
   Dim row_1 As Integer
   Dim range_1 As Range
   row_1 = Cells(Rows.Count, "B").End(xlUp).Row
   Set range_1 = Range("B5:B" & row_1)
   Names.Add Name:="NameSheet_1", RefersTo:=range_1
End Sub
Step 2:
- Now, run the VBA code by pressing F5.
In this method, we named a range from Cell B5 to the last cell of Column B with data.
Read More: How to Change Excel Column Name from Number to Alphabet (2 Ways)
5. Create a Named Range and Highlight It
In this method, we will from a named range and highlight that.
Step 1:
- Select the cells of the Salary column.
- Enter the command module by pressing Alt+F11.
- Put the following VBA code on the command module.
Sub Create_Named_Range_5()
Dim range_1 As String
range_1 = "Salary_1"
Names.Add Name:=range_1, RefersTo:=Selection
Range("Salary_1").Value = 1000
Range("Salary_1").Interior.Color = vbBlue
End Sub
Step 2:
- Run the code by pressing F5.
Read More: How to Edit Defined Names in Excel (Step-by-Step Guideline)
How to Delete a Named Range Using Excel VBA
We already learned how to name a range. Now we will show how to delete a named range.
Step 1:
- Hit Alt+F11 to enter the VBA command module.
- Copy the following code and paste it onto the module.
Sub Delete_Named_Range()
Dim name_range As Name
Dim count_range As Long
 UserAnswer = MsgBox("Do you like to avoid Print Areas?", vbYesNoCancel)
   If UserAnswer = vbYes Then SkipPrintAreas = True
   If UserAnswer = vbCancel Then Exit Sub
 On Error GoTo Skip
 For Each name_range In ActiveWorkbook.Names
If SkipPrintAreas = True And Right(name_range.Name, 10) = "Print_Area" Then GoTo Skip
     On Error GoTo Skip
     name_range.Delete
     count_range = count_range + 1
Skip:
     On Error GoTo 0
 Next
 If count_range = 1 Then
   MsgBox "[1] name range is removing"
Else
   MsgBox "[" & count_range & "] name range are removing"
End If
End Sub
Step 2:
- Press F5 to run the code.
- A new window will appear.
Step 3:
- Click Yes if you agree. Another window will appear mentioning how many names will be removed.
Step 4:
- Click OK.
We can see that all the names are removed already.
Read More: [Fixed!] Name Manager Delete Option Greyed out in Excel (3 Reasons)
Conclusion
In this article, we explained how to create a named range from the selection in Excel. We also added other methods to create a named range and delete a named range with Excel VBA. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
