Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 712
  • 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...
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
Don't worry about the extra points. I'm glad I was able to help.

Marty - MVP 2009 to 2012

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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