Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Determining next row in Excel

    I don't have much experience with Excel macros and I need to write something rather simple.

    How do I determine what the 'next' row is in the active worksheet? Say rows 1 - 12 have been filled with data, how can I determine that row 13 is the next blank row?

    Thanks,

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Richard,

    This will do the trick assuming your data is filling from the top of the sheet (row 1). If the data has embedded blank lines these will be ignored with this method.

    Code:
    Option Explicit
    
    Sub NextBlank()
    
       Dim lLastRow As Long
       
       lLastRow = Application.Rows.Count  '*** Gets No. rows in your version of Excel ***
       
       Cells(lLastRow, 1).Select
       Selection.End(xlUp).Offset(1, 0).Select
       MsgBox "The Next Blank Row is:" & ActiveCell.Row, _
              vbOKOnly + vbInformation, "What's Next?"
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just as an FYI, that does assume that column A is populated for all rows of data. There's also no need to select anything:
    Code:
    msgbox cells(rows.count, "a").end(xlup).row + 1
    Last edited by rory; 2011-07-28 at 16:56. Reason: tags
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    I like the no selection part!
    However, I fail to see how your solution does away with the need to assume one column has no missing data?
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It doesn't - they were two separate comments. (Sorry, I should have been clearer)Since we don't know if missing data is an issue, I didn't address that - you would usually use Find for that.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts
    Thanks to both of you.

Posting Permissions

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