• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 714
  • Last Modified:

Last Column From Named Range

Hello All,

Is there a VBA way to find this: this function takes in a named range as argument or any other way and then spits out the first and the last column letters for the range…can this be done?

returns first column letter and last column letter
3 Solutions
RayneAuthor Commented:
Saqib Husain, SyedEngineerCommented:

Function nyFunc(x)
nyFunc = Range(ActiveWorkbook.Names(x)).EntireColumn.Address(False, False)
End Function
Michael VasilevskySolutions ArchitectCommented:
Try as a place to start:

Function MyFunction(strRangeName As String) As String

    MyFunction = ThisWorkbook.Names(strRangeName).RefersTo
End Function

Then you'll have to had some string manipulation to extract the column letters...
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Martin LissOlder than dirtCommented:
Building on mvasilevsky's code here is complete code.

Private Sub CommandButton1_Click()
Dim strCols As Variant
strCols = MyFunction("MyRange")
MsgBox "First column is " & strCols(0)
MsgBox "Last column is " & strCols(1)
End Sub
Function MyFunction(strRangeName As String) As Variant

    Dim strRange As String
    Dim strParts() As String
    Dim strLetter() As String
    Dim ArrOut(1) As String
    strRange = ThisWorkbook.Names(strRangeName).RefersTo
    strParts = Split(strRange, ":")
    strLetter = Split(strParts(0), "$")
    ArrOut(0) = strLetter(1)
    strLetter = Split(strParts(1), "$")
    ArrOut(1) = strLetter(1)
    MyFunction = ArrOut
End Function

Open in new window

RayneAuthor Commented:
I had to increase the overall points to ensure each of you gets points for effort...thank you so much for your diligence and timely help :)
RayneAuthor Commented:
Increased points
RayneAuthor Commented:
RayneAuthor Commented:
Hmm, I tried to increase the points several times but the site will not let me do that but it still shows the option to increase the points...this is site bug but anyways thank you all

Martin LissOlder than dirtCommented:
Don't worry about the extra points. I'm glad I was able to help.

Marty - MVP 2009 to 2012
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now