Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last used cell in row (2000)

    This is probably dead easy, but I can't seem to locate the appropriate property. I want the column number of the last used (non-empty) cell in the (current) row of the selected cell. I thought of the .End function using something like these routines:

    ' Find the last cell, before a blank in a Row:
    Sub LastCellBeforeBlankInRow()
    Range("A1").End(xlToRight).Select
    End Sub

    ' Find the very last used cell in a Row:
    Sub LastCellInRow()
    Range("IV1").End(xlToLeft).Select
    End Sub

    but I don't want it for navigation - rather for use as a loop parameter. The row may contain scattered empty cells before the "last used" cell and I hear that this can cause problems for the .End function.

    thanks

    Alan

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

    Re: Last used cell in row (2000)

    The first method won't work if there are empty cells before the last non-empty cell. To get a reference to the last non-empty cell without selecting it, use

    Cells(ActiveCell.Row, 256).End(xlToLeft)

    Note that this is not a valid instruction by itself, you must do something with it, for example

    Dim intCol As Integer
    intCol = Cells(ActiveCell.Row, 256).End(xlToLeft).Column

    You can replace ActiveCell by another cell reference if needed.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last used cell in row (2000)

    Beautiful! Thanks Hans... and in record time too, I believe <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Alan

Posting Permissions

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