Consulting

Results 1 to 7 of 7

Thread: Solved: Finding Last column in Row

  1. #1
    VBAX Regular
    Joined
    Jun 2008
    Location
    California
    Posts
    89
    Location

    Red face Solved: Finding Last column in Row

    Hi,

    I know how to find the last row in a column:

    [vba]

    MyLoc = ActiveCell.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False)
    MyLoc = Left(MyLoc, Len(MyLoc) - 2)

    Range(MyLoc & Rows.Count).End(xlUp).Select

    [/vba]
    Now I just need to know how to find the last column in a row where there are blank cells in the row preventing me from doing:
    [vba]
    Selection.end(xltoRight).select
    [/vba]

    Please help!!!! :-)

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    [vba]lastcolumn = Cells(Selection.Row, Columns.Count).End(xlToLeft).Column
    MsgBox lastcolumn[/vba]or
    [vba]Cells(Selection.Row, Columns.Count).End(xlToLeft).Select[/vba]
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    Jun 2008
    Location
    California
    Posts
    89
    Location
    Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Can I use "selection.column" when trying to find the last row of a column????
    As you can see from above in my first entry I put in a couple of lines of code to just get the column...

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Yes:
    [vba]cells(rows.count,selection.column).end(xlUp).select[/vba] but you'd have got a quicker answer if you'd tried it!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    These solutions assume a single cell selection which may be misleading

    If your Selection was A1:A10 and A5 was the longest row, you will return the rightmost cell in Row 1.


    [vba]
    Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Select
    [/vba]
    would return a valid answer for the active cell.



    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    VBAX Regular
    Joined
    Jun 2008
    Location
    California
    Posts
    89
    Location
    I did try it but what I was doing didn't work. I see that i had to switch a few things around... So thanks again...

  7. #7
    VBAX Regular
    Joined
    Jun 2008
    Location
    California
    Posts
    89
    Location
    Now I see what I did wrong... duh... I do have a headache today... :-)

Posting Permissions

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