Get Column Name from Range

[AlexanderBB]

If a range is

Set MyRange = Application.Range("Sheet1!A2:G2")

Is it possible to get the name (Header) of the column anyhow ?
I tried ? MyRange(2).Name but no go.

 

[MARK858]

Is it possible to get the name (Header) of the column anyhow ?
I tried ? MyRange(2).Name but no go.

Which column as your range covers 7 columns? and is row 2 the row with the headers?

 

[AlexanderBB]

As I understand it, MyRange will contain contents of Cols A to G on row 2 with MyRange(2) = Col B, Row 2
The Header names are in Row 1.
I don't think they will exist in "MyRange" but wanted to be sure...

 

[MARK858]

If You mean for instance the value in C1 from MyRange then

Sub xxxx()
Dim MyRange As Range
Set MyRange = Application.Range("Sheet1!A2:G2")
Debug.Print MyRange(0, 3)
End Sub

 

[AlexanderBB]

Wow, brilliant. That's exactly it, will simplify my code no end.
Thank you

 

[MARK858]

Might be worth you having a read of the article in the link below that Alan Beban wrote (reproduced on Chip Pearsons site)

http://www.cpearson.com/Excel/cells.htm