Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    go to last non-blank row (xl2003sp2)

    This code
    Application.ScreenUpdating = False
    On Error Resume Next
    ActiveSheet.ShowAllData
    Range("G3").Select
    sits behind a button removing one or more filters.
    How do I modify it to make the active cell not G3, but the first available blank cell in column G?
    Cells in column G are populated with stuff from top to bottom frequently, so the first blank cell is a moving target.

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

    Re: go to last non-blank row (xl2003sp2)

    Try

    Range("G1").End(xlDown).Offset(1, 0).Select

    or

    Range("G65536").End(xlUp).Offset(1, 0).Select

    BTW your question is different from the subject: go to the first blank row vs go to the last non-blank row.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: go to last non-blank row (xl2003sp2)

    Hi John

    This should work

    <pre>Sub FindLastCell()
    Dim LastCell As Range
    With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, "G").End(xlUp)
    If IsEmpty(LastCell) Then
    'do nothing
    Else
    Set LastCell = LastCell.Offset(1, 0)
    End If
    End With
    LastCell.Select
    End Sub</pre>


    Obviously adapt to your needs
    Jerry

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

    Re: go to last non-blank row (xl2003sp2)

    Actually, he is asking for the first empty cell in column G which may not be in the first blank row.
    Legare Coleman

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

    Re: go to last non-blank row (xl2003sp2)

    I interpreted "Cells in column G are populated with stuff from top to bottom" to mean that there wouldn't be gaps.

    But let's see what John Rose has to say about the replies.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: go to last non-blank row (xl2003sp2)

    Thank you very much, all three.
    And to Hans for pointing out the inconsistency.
    It is indeed the first 'empty' cell I want (although I've seen discusssions here concerning whether any cell is truly 'empty'.)
    And yes, Legare, there are no gaps in column G.
    I hate gaps in the sense of completely 'empty' rows - just get in the way and stop good things happening, eg populating a whole column with the same formula by the doubleclick the fill handle trick.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: go to last non-blank row (xl2003sp2)

    Heh, Hans, I think I understand this: it says -
    go to the last possible cell in the column
    then go up until you hit something
    then go back one and put a big black border round the cell (but only a temporary one)

    True or False?

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: go to last non-blank row (xl2003sp2)

    But, regretfully, doesn't work.
    Or if it does, the first blank cell in column G (my target) is three storeys below the floor, not in the middle of my screen.
    This is my code, with Hans' improvement
    Sub show_all()
    '
    ' show_all Macro
    ' Macro recorded 7/10/2003 by John Rose
    '

    '
    Application.ScreenUpdating = False
    On Error Resume Next
    ActiveSheet.ShowAllData
    Range("G65536").End(xlUp).Offset(1, 0).Select
    End Sub

    Idiot's guide error correction, please?

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: go to last non-blank row (xl2003sp2)

    John

    Have a look as <post:=674,038>post 674,038</post:>
    Jerry

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

    Re: go to last non-blank row (xl2003sp2)

    You can add this line just before the End Sub:

    ActiveWindow.ScrollRow = ActiveCell.Row

  11. #11
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: go to last non-blank row (xl2003sp2)

    Very Nearly, Hans!
    Rows 1 nd 2 are a parking slot for buttons (and a label) and a row of labels.
    This code

    Application.ScreenUpdating = False
    On Error Resume Next
    ActiveSheet.ShowAllData
    Range("G65536").End(xlUp).Offset(-5, 0).Select
    ActiveWindow.ScrollRow = ActiveCell.Row

    shows 5 rows of populated cells beneathe static rows 1 & 2, with the active cell 1 click away from my target.
    Is there a modification to the last line of code to move the active cell 6 rows down but still on the screen?

    **Jezza** thanks no end, but I couldn't make your thing work. This is a property of me and not of you.

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

    Re: go to last non-blank row (xl2003sp2)

    Try
    <code>
    ...
    Dim lngRow As Long
    Range("G65536").End(xlUp).Offset(1, 0).Select
    lngRow = ActiveCell.Row - 6
    If lngRow < 1 Then lngRow = 1
    ActiveWindow.ScrollRow = lngRow
    </code>
    The code select the first blank cell, but puts the top of the window 6 rows higher (with a little check to avoid errors if there are few data)

  13. #13
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: go to last non-blank row (xl2003sp2)

    Tried, executed, saved and implemented.
    Word Perfect (**grin**), Hans.
    Thanks no end.

Posting Permissions

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