Return the address of a specific column in named range

[Godfather]

I am trying to write some VBA to return the address of a specific column in a named range in my worksheet. For example, right now I am using the following code to return the whole range address of $I$66:$J$75, but I only want $I$66 to show. Could anyone please help me on this?

Function NamedRangeAddress(NamedRange As Range)

NamedRangeAddress = Range(NamedRange).Address

End Function

Thanks!

 

[Andrew Poulsom]

I can't get the function you posted to work, but if you want the first cell of a named range:

Function NamedRangeAddress(NamedRange As Range)
    NamedRangeAddress = NamedRange.Cells(1, 1).Address
End Function

 

[Godfather]

Maybe I need to be a bit clearer on what my function was doing. It was looking inside the cell "NamedRange" to read the contents and return the address of the name of the range which is located inside the cell "NamedRange." However, I need the address of only the first column because I am trying to use the range address the funciton returns to create a data validation list.

Thanks!

 

[Godfather]

Nevermind, I just figured it out. I used the following code:

Function NamedRangeAddress(NamedRange As Range)

NamedRangeAddress = Range(NamedRange).Columns("A").Address

End Function

Thanks

 

[Andrew Poulsom]

Oh I see. If the name Temp refers to I66:J75 and A1 contains the text Temp, try:

=NamedRangeAddress(A1)

with:

Function NamedRangeAddress(NamedRange As Range)
    NamedRangeAddress = Range(NamedRange).Cells(1, 1).Address
End Function