Naming a range of cells

Knight of Nee

Board Regular
Joined
Aug 4, 2003
Messages
124
Hi.

Is there a way to rename a range of cells using a macro. however there is a twist i want the name to be the same as the contents of a cell. e.g if A1 = "Cat", then i want a range of cells to be called "cat", however if it equals "Dog" i want the range to be called "Dog".

Thanks for any help
Craig
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Craig:

The following code will create a range name for the selected cell giving the range the same name as the contents of the selected cell ...
Code:
    Range("A1").Select
    ActiveWorkbook.Names.Add Name:=[A1], RefersTo:="=Sheet4!A1"
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Craig:

And if you want to create a range name for a range of cells, say A1:A6 then the following should do it ...
Code:
Range("A1:A6").Select
ActiveWorkbook.Names.Add Name:=[A1], RefersTo:=Selection
 

Knight of Nee

Board Regular
Joined
Aug 4, 2003
Messages
124

ADVERTISEMENT

along the same lines, how do i delete a named range of cells using a macro?
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
ActiveWorkbook.Names("MyNamedRange").Delete

Which you could have gotten yourself by using the macro recorder. Substitute "MyNamedRange" for whatever name the range has been given.
 

Mrexceluserid

New Member
Joined
Mar 17, 2004
Messages
2

ADVERTISEMENT

I have a question in regarding the same topic.
In the folloing macro, it is taking the name of the range from cell A1. Here we are naming only one range of cells. Now my requirement is to run through several cells in the excel sheet using a for loop in the macro and pick up the value in each cell, and then give the name to that cell based on that value inside.

Range("A1:A6").Select
ActiveWorkbook.Names.Add name:=[A1], RefersTo:=Selection

For example, say I have 15 cells starting at A1 and each cell says Row1, Row2, Row3 etc. Now I want to run through A1 to A15, name A1 as Row1, A2 as Row2 etc.

Could you please help me with this.
Many thanks
 

Mrexceluserid

New Member
Joined
Mar 17, 2004
Messages
2
I have a question in regarding the same topic.
In the folloing macro, it is taking the name of the range from cell A1. Here we are naming only one range of cells. Now my requirement is to run through several cells in the excel sheet using a for loop in the macro and pick up the value in each cell, and then give the name to that cell based on that value inside.

Range("A1:A6").Select
ActiveWorkbook.Names.Add name:=[A1], RefersTo:=Selection

For example, say I have 15 cells starting at A1 and each cell says Row1, Row2, Row3 etc. Now I want to run through A1 to A15, name A1 as Row1, A2 as Row2 etc.

Could you please help me with this.
Many thanks
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi,

How about:
Code:
Sub Test()
    Dim rngData As Range, rngCell As Range
    
    With ThisWorkbook
        Set rngData = .Worksheets("Sheet1").Range("A1:A15")
        For Each rngCell In rngData
            .Names.Add Name:=rngCell.Value, _
                RefersTo:="=" & rngCell.Parent.Name & "!" & rngCell.Address
        Next rngCell
    End With
    
End Sub
HTH
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,085
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top