Last Column From Named Range

Posted on 2012-09-21
Last Modified: 2012-09-21
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
Question by:Rayne

    Author Comment

    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed

    Function nyFunc(x)
    nyFunc = Range(ActiveWorkbook.Names(x)).EntireColumn.Address(False, False)
    End Function
    LVL 10

    Assisted Solution

    by:Michael Vasilevsky
    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...
    LVL 44

    Accepted Solution

    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


    Author Comment

    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 :)

    Author Comment

    Increased points

    Author Comment


    Author Comment

    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

    LVL 44

    Expert Comment

    by:Martin Liss
    Don't worry about the extra points. I'm glad I was able to help.

    Marty - MVP 2009 to 2012

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now