Need help with my code. Thanks

[pedie]

Hi everyone!
I have made a sample code that is only for col C:C.
In row 3 i have heading that I want it to be a range name. eg like help_1 etc
Whatever word in there in row 3 for each col i want the code to pick that up and then name the range select the whole colyumn....
The code will continue to name range untill the 3 row (moving column wise to right) is empty...
Thanks alot for helping!
Pedie

Sample code below! works only for one col

Sub try1()
Sheet1.Select
    Columns("C:C").Select
    ActiveWorkbook.Names.Add Name:=Range("C3").Value, RefersToR1C1:="=Sheet1!C3"
End Sub

 

[Norie]

pedie

Do you mean you want to use the values in row 3 of each column to create a named range that refers to that column and/or all the data in it?

PS The code you posted only works for one cell as far as I can see.

 

[pedie]

Norie, that is correct.
Value in row 3 will be used as a name for that col.
If there is no value in row 3 then quit the code or find next row3.value (move right >>>) and do the same for each col....

 

[Norie]

Something like this perhaps.

Sub CreateNames()
Dim rng As Range
 
    Set rng = Range("C3")
 
    While rng.Value <> ""
        Range(rng.Offset(1), rng.End(xlDown)).Name = rng.Value
        Set rng = rng.Offset(, 1)
    Wend
 
End Sub

By the way there is a built in method for doing this via Insert>Names... but I always forget exactly how it works.

Found code for it though.

    Range("C3").CurrentRegion.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

 

[pedie]

Norie, Great job....works perfect. Thanks again!!!

 

[pedie]

Something like this perhaps.

Sub CreateNames()
Dim rng As Range
 
    Set rng = Range("C3")
 
    While rng.Value <> ""
        Range(rng.Offset(1), rng.End(xlDown)).Name = rng.Value
        Set rng = rng.Offset(, 1)
    Wend
 
End Sub

By the way there is a built in method for doing this via Insert>Names... but I always forget exactly how it works.

Found code for it though.

    Range("C3").CurrentRegion.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

So kind of you Norie...

'i change it to this -2...so it will select the whole col...lol...the first code itself is perfect...thanks again
Range(rng.Offset(-2), rng.End(xlDown)).Name = rng.Value