How to get the column letter of the rightmost used column

March 1st, 2008
Public Function GetLastColumnLetter() As String
    Dim strColumnLetter As String
    Dim ipos As Integer
   
    With ActiveSheet.Range("IV1").End(xlToLeft).EntireColumn
        GetLastColumnLetter = .Address(False, False)
    End With
    ipos = InStr(1, GetLastColumnLetter, ":")
   
    GetLastColumnLetter = Left(GetLastColumnLetter, ipos - 1)

End Function

Sub Test()
    MsgBox GetLastColumnLetter()
End Sub

Posted in Excel, How To, VBA | 3 Comments




3 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Thank you for this tip!!

    Comment by Andrew — March 1, 2008 #

  2. ActiveSheet.Range(”IV1″).End(xlToLeft)
    Plz explain “IV1″.

    Comment by andeep — November 23, 2009 #

  3. Hi andeep, in Excel 2003, where a worksheet has 256 columns, the rightmost column is IV. So “IV1″ is the first cell in column IV. The code will work only on Excel 2003 and earlier versions. Thanks for stopping by!

    Comment by Vergel — November 23, 2009 #

Leave a comment



(C) by Virgilio Adriano. All rights reserved. Powered by WordPress.
Entries and comments feeds.
It took 0.435 seconds to load this page.