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

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.

Excel VBA to Create a Named Range

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

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.

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

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 put 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.

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

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

Create a Named Range and Highlight ItStep 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

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 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.


Related Articles

Alok Paul

Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo