Naming a range of cells

[Knight of Nee]

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

 

[Yogi Anand]

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

    Range("A1").Select
    ActiveWorkbook.Names.Add Name:=[A1], RefersTo:="=Sheet4!A1"

 

[Yogi Anand]

Craig:

And if you want to create a range name for a range of cells, say A1:A6 then the following should do it ...

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

 

[Knight of Nee]

cheers for your help mate

 

[Knight of Nee]

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

 

[Tom Urtis]

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]

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]

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)]

Hi,

How about:

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