Find first blank row after all other data in sheet

  • This function will return the first blank row that comes after any other data on the worksheet sent to it as a parameter.

    A call to it might be something like this to get the cell in column A:

    Dim r1 as Range
    Set r1 = Cells(firstUnusedCellRowNumber(ActiveSheet), 1)

    If the worksheet is empty, the function returns row 1. If the last of the data is in B13, the function returns row 14. If everything is blank except for row 13, it still returns 14.

    This can be helpful if you would rather use

    Cells(Rows.Count, 1).End(xlUp).Row

    but you aren't sure which column has the last row of data.

  • Re: Find first blank row after all other data in sheet

    My comment is about "UsedRange". It includes cells/rows/columns with formatting but otherwise would be blank. I have been relying on the following for a while now:

    It assumes the active sheet and column 1 (A) as the default. But, like the comment in the code says, it will not find it if it's hidden due to filtering.

    Regards, Jim

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!