Excel VBA to Create Named Range from Selection (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

A named range is to set a name for one or more cells. We can call a 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.


Excel VBA to Create Named Range from Selection: 5 Examples

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.

Excel VBA to Create a Named Range

The VBA command module will appear. We will write the VBA code on this module, and then run that code.

Step 2:

  • First, select the range where to create the named range. The name bar does not show 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 the name of the range is showing now.

Read More: How to Set Value to a Named Range with Excel VBA


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

Cell Reference Directly in VBA Code to Create Named Range

Step 2:

  • Hit the F5 button to run the code.

Cell Reference Directly in VBA Code to Create Named Range

We can see the name of the range here.


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

Form Named Range from User’s Input in Excel VBA

Step 2:

  • Press F5 to run the code.
  • A window will appear to choose the range. Select the range from the dataset.

Form Named Range from User’s Input in Excel VBA

Step 3:

  • Then, click OK.
  • A new window will appear putting the name of the range.

Form Named Range from User’s Input in Excel VBA

Step 4:

  • Again, click OK.

The name is shown here.


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

Create a Named Range of Column Using Excel VBA 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.


5. Create a Named Range and Highlight It

In this method, we will choose 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

Create a Named Range and Highlight It Step 2:

  • Run the code by pressing F5.

Read More: Create Dynamic Named Range with VBA in Excel


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

Delete a Named Range Using Excel VBA

Step 2:

  • Press F5 to run the code.
  • A new window will appear.

Delete a Named Range Using Excel VBA

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 have been removed already.

Read More: Excel VBA to Delete Named Range


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we explained how to create a named range from the selection in Excel. We also added other methods to create and delete a named range with Excel VBA. I hope this will satisfy your needs. You can give your suggestions in the comment box.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo