Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Rightmost column (Excel XP)

    I've been using...

    lLastRow = ActiveSheet.Range("A65536").End(xlUp).Row - 1

    to find the last row of an excel sheet. How do i find the last (rightmost) column? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rightmost column (Excel XP)

    lLastCol = ActiveSheet.Range("IV1").End(xltoLeft).Column

    I don't understand where your "-1" is from in your row calc. The last row is:
    lLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    The next (blank row) is:
    lLastRow = ActiveSheet.Range("A65536").End(xlUp).Row +1

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rightmost column (Excel XP)

    He probably meant the offset to the last row.
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rightmost column (Excel XP)

    I've also tried this but don't know how to make the number the letter of the column. For instance, lColumn is a 12 then how do i make it an L? thank you.

    lColumn = ActiveSheet.UsedRange.Columns.Count

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rightmost column (Excel XP)

    lColumn = chr(ActiveSheet.UsedRange.Columns.Count+64)

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rightmost column (Excel XP)

    That's not going to work very well past column Z.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rightmost column (Excel XP)

    If you want the column letters for the last used column, then you could use something like this:

    <pre>Dim strLColumn As String
    strLColumn = Left(ActiveSheet.Range("IV1").End(xlToLeft).Addres s(False, False), _
    Len(ActiveSheet.Range("IV1").End(xlToLeft).Address (False, False)) - 1)
    </pre>

    Legare Coleman

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rightmost column (Excel XP)

    D'oh!

    Steve

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rightmost column (Excel XP)

    It keeps returning an A even where there are 10 columns?

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rightmost column (Excel XP)

    How about
    <pre> With ActiveSheet.UsedRange
    MsgBox Split( _
    ActiveSheet.Cells(1, .Column + .Columns.Count - 1).Address, _
    "$")(1)
    End With</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rightmost column (Excel XP)

    UsedRange is not accurate when you are editing a worksheet. If you fill some cells, UsedRange is expanded, but when you clear them, UsedRange doesn't contract until the file is saved. See my reply above.

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Rightmost column (Excel XP)

    That gives the last column with an empty but formatted cell, however I was on a similar track:

    Sub LastUsedCol()
    Dim IntC As Integer, intL As Integer
    For IntC = ActiveSheet.UsedRange.Columns.Column + _
    ActiveSheet.UsedRange.Columns.Count To 1 Step -1
    With Columns(IntC).EntireColumn
    On Error Resume Next
    intL = .SpecialCells(xlCellTypeConstants, 23).Count
    On Error Resume Next
    intL = intL + .SpecialCells(xlCellTypeFormulas, 23).Count
    If CBool(intL) Then
    MsgBox Split(Columns(IntC).Address(False, False), ":")(1) ' This line corrected
    Exit For
    End If
    End With
    Next IntC
    End Sub

    There -has- to be a better way ...
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rightmost column (Excel XP)

    But, jha is running XP. I think UsedRange is fixed since XL2K.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Rightmost column (Excel XP)

    Hans, are you sure this returns correct results if the bottom-most row is a higher number than the number of the leftmost column? I'm not getting correct results on a sheet where cells D14 (row 14) and L2 (column 12) are the only cells with content.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rightmost column (Excel XP)

    Hi Sam,

    You're correct that recent versions of Excel keep better track of deleted cells. But, as JohnBF remarks, the UsedRange includes empty but formatted cells.

    Jha can choose what he needs: if he wants "empty" to be "no contents, no formatting", your code is the way to go, but if he wants "no contents only", one of the longer versions is needed.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •