How to name a range with a cell content

[eoliann]

Hello,
How to name a range like B1:B21 with B1 content, but in VB?
I need to rename also C2:C21 with C1 content and D2:D21 and so.
The lists name is used in Data Validation.
Thankyou!

 

[Jonmo1]

Welcome to the board.

Try

Range("B1:D21").CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

 

[eoliann]

It Works. Thankyou a lot! Suuuuper!

 

[eoliann]

How is make this script to run automaticaly once the data is added in new columns?
(Sorry for my bad English)

 

[Jonmo1]

I think it would be best to run it manually as needed.

If you ran it automatically, how would it know the limit of the range?
Is it ready to be named after just 1 value is added to the column, or not till it's full to the end?

It could be adjusted to allow you to 'select' the range to name when you run it..

Sub test()
Dim MyRange As Range
Set MyRange = Application.InputBox("Select range to name", , , , , , , 8)
MyRange.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
End Sub

 

[eoliann]

I found the solution. I added a button with code:

Private Sub Add_Info_Click()
Range("A2:D26").CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
Application.ScreenUpdating = True
End Sub

 

[eoliann]

Thanks again!

 

[Jonmo1]

You're welcome.