VBA for creating many named ranges

[DutchKevin]

Dear All,
I would like to use VBA to create named ranges from an extensive table, but i dont know how to write this specific code.
The names to be used as the name for the named ranges are in row6, starting at column B onwards to the right.
The data that should be in the range is varying in lenght. It is positioned in the column below the proposed name.
Some ranges will only be 3 cells long/high, others can be as long as 12 or 15 cells

How would a VBA code look like that scans this data and creates the named ranges accordingly? The named ranges should be available in the whole workbook.
Is it possible to also use such code to apply changes to the ranges? So if a column gets longer or shorter as the amount of cells in a range changes, the code can be run to reflect that in the named ranges?

Thansk for looking at this

Best Regards

 

[Norie]

Perhaps.

Sub MakeNames()
Dim rngCol As Range
Dim rng As Range

     Set rngCol = Range("B6")

     Do Until rngCol.Value = ""

         Set rng = Range(rngCol.Offset(1), Cells(Rows.Count, rngCol.Column).End(xlUp))
         rng.Name = rngCol.Value
 
         Set rngCol = rngCol.Offset(,1)
     Loop

End Sub

 

[DutchKevin]

Hi,
thanks for the suggestion
it now gives an error at:
rng.Name = rngCol.Value

 

[Norie]

Any particular error?

Are all the values in row 6 valid names?

 

[DutchKevin]

Hi Norie,
there was an invalid charachter in one of the names, so that solved it.
further it now works very quick and smooth.
It also allows to update the ranges once the data changes. i was afraid it woudl block there, but it gave no problems.
Thanks for your help!