Dynamic name for a dynamic range

[Charles Bushby]

Does anyone know how to dynamically change the name of a range? Say a range was called Apples which is taken from the column header and I wanted to change it to Pears by changing the column header. Is there a way to do this?

 

[VeBeYay]

Hi,
Try this. Assuming the column header is in cell(1,1)

Dim str As String

str = Cells(1, 1).Value

Names.Add str, Range("B2:D4")