Find first blank row after all other data in sheet

shknbk2 · Mar 4th 2016

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

Code

Function firstUnusedCellRowNumber(sh As Worksheet) As Long
    With sh.UsedRange
        If .Cells(1, 1).Address <> .Cells(.Rows.Count, .Columns.Count).Address Then
            firstUnusedCellRowNumber = .Rows.Count + .Row
        Else
            If .Cells(1, 1).Value <> "" Then
                firstUnusedCellRowNumber = .Row + 1
            Else
                firstUnusedCellRowNumber = 1
            End If
        End If
    End With
End Function

Display More

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

Code

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

Code

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

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

jdawson422 · Apr 11th 2016

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:

Code

Function LastRow(Optional ByVal InColNum = 1) As Integer
    'This does not find it if it's hidden due to filtering
    Dim fndRng As Range
    With Range(Cells(InColNum, InColNum), Cells(InColNum, InColNum)).EntireColumn
        Set fndRng = .Find(What:="*", _
                After:=Cells(InColNum, InColNum), _
                SearchDirection:=xlPrevious, _
                SearchOrder:=xlByColumns)
    End With
    
    If Not fndRng Is Nothing Then
        LastRow = fndRng.Row
    Else
        LastRow = 0
    End If
End Function

Display More

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

Find first blank row after all other data in sheet

Participate now!

Share