Find next non blank row

[Formula11]

How can you find the next non blank row after the active cell.

Tried this.

Sub test()
    Dim Row As Integer
    Dim non_blank_row As Integer
    
    Row = (ActiveCell.Row)
    non_blank_row = Row
    
    While WorksheetFunction.CountA(Row) = 0
        non_blank_row = non_blank_row + 1
    Wend
    
    MsgBox non_blank_row
End Sub

 

[Akuini]

Try this:

Sub Formula11()

Dim non_blank_row As Long
Dim c As Range, g As Range
Set g = Cells(ActiveCell.Row + 1, "A")
If g = "" Then
Set c = ActiveSheet.Cells.Find(What:="*", After:=g, LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If c.Row >= g.Row Then
        non_blank_row = c.Row
    End If
Else
    non_blank_row = g.Row
End If

    MsgBox non_blank_row

End Sub

Note: non_blank_row should be declared as Long instead of Integer, because Integer range values is only -32,768 to 32,767.

 

[Formula11]

Thanks Akuini, this works great.
I'll change to Long as well.

 

[Formula11]

I've used the code above as part of a macro to insert the required number of rows until the first non-blank row is past the next page break.
The first few lines are a much bigger font size.
The code guesses the number of lines required (assuming all font sizes are the same).
Then there is a Do Until loop inserting one line at a time. I tried a few things and found the only thing that worked all the time was to insert one line at a time.

This code is working but it's very slow, because of the large fonts, there's a few iterations.

I was wondering how this could be made faster.

Sub insert_rows()
    Dim Row As Long, Column As Long
    Dim start_cell As Range
    Dim next_NonBlank_cell As Range
    Dim next_NonBlank_row As Long
    Dim next_page_row As Long
    Dim new_rows As Long
    'start
    Row = (ActiveCell.Row): Column = (ActiveCell.Column)
    Application.ScreenUpdating = False
    On Error GoTo line_exit
    'FIRST PASS
    '----------
    Set start_cell = Cells(Row, 1)
    If start_cell = "" Then
        Set next_NonBlank_cell = ActiveSheet.Cells.Find(What:="*", After:=start_cell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If next_NonBlank_cell.Row >= start_cell.Row Then
            next_NonBlank_row = next_NonBlank_cell.Row
        End If
    Else
        next_NonBlank_row = start_cell.Row
    End If
    'loc. next page break
    next_page_row = Row
    While Rows(next_page_row).PageBreak = xlNone
        next_page_row = next_page_row + 1
    Wend
    'number of rows to insert
    new_rows = next_page_row - next_NonBlank_row
    If new_rows < 0 Then
        Exit Sub
    End If
    Rows(Selection.Cells(1).Row & ":" & Selection.Cells(1).Row + new_rows - 1).Insert Shift:=xlDown
    'format ... required
    Rows(Row & ":" & Row + new_rows).Select
    With Selection
        .NumberFormat = "General"
        .WrapText = False
        .Style = "Normal"
    End With
    'select initial cell
    Cells(Row, Column).Select
    'NEXT PASSES ... if req
    '----------------------
    'repeat process to account for variable row height
    Do Until next_NonBlank_row = next_page_row
        'loc next non-blank row
        Set start_cell = Cells(Row + 1, 1) 'Go down one row for iterations to avoid formatting
        If start_cell = "" Then
            Set next_NonBlank_cell = ActiveSheet.Cells.Find(What:="*", After:=start_cell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If next_NonBlank_cell.Row >= start_cell.Row Then
                next_NonBlank_row = next_NonBlank_cell.Row
            End If
        Else
            next_NonBlank_row = start_cell.Row
        End If
        'loc. next page break
        next_page_row = Row
        While Rows(next_page_row).PageBreak = xlNone
            next_page_row = next_page_row + 1
        Wend
        'number of rows to insert
        If next_NonBlank_row < next_page_row Then
            Rows(Row).Insert 'one by one as it's tricky
        End If
        'select initial cell
        Cells(Row, Column).Select
    Loop
    'End
    Application.ScreenUpdating = True
line_exit:
End Sub

 

[Akuini]

I've used the code above as part of a macro to insert the required number of rows until the first non-blank row is past the next page break.

So you want to shift the first non-blank row (below the active cell) to the first row of the next page?
How about this:
Say maximum number of rows in a page is 50:
nr = 50 'number of rows in a page won't exceed this number, change to suit
The code will insert 50 rows below the active cell, shifting the first non-blank row to the next page then delete some rows to shift the first non-blank row to the first row of the next page.
If it works I'll write some comments in the code for better understanding, if you need it.

Sub Formula11_B()
Dim HPB As HPageBreak
Dim non_blank_row As Long
Dim c As Range, g As Range
Dim x As Long, y As Long, n As Long

Application.ScreenUpdating = False
nr = 50 'number of rows in a page won't exceed this number, change to suit
k = ActiveCell.Row + 1

Rows(k & ":" & k + nr).Insert Shift:=xlDown
    With Rows(k + 1 & ":" & k + nr - 1)
        .NumberFormat = "General"
        .WrapText = False
        .Style = "Normal"
    End With

n = ActiveCell.Row
    ActiveWindow.View = xlPageBreakPreview
        For Each HPB In ActiveSheet.HPageBreaks
            If HPB.Location.Row > n Then x = HPB.Location.Row: Exit For
        Next
    ActiveWindow.View = xlNormalView

Set g = Cells(ActiveCell.Row + 1, "A")

If g = "" Then
Set c = ActiveSheet.Cells.Find(What:="*", After:=g, LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If c.Row >= g.Row Then
        non_blank_row = c.Row
    End If
Else
    non_blank_row = g.Row
End If

y = non_blank_row - x
If y > 0 Then Cells(x, "A").Resize(y, 1).EntireRow.Delete
Application.ScreenUpdating = True

End Sub

 

[Formula11]

Thanks again Akuini.
This is much faster and very clever method.
Cheers.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.
Note: you should not use Row & Column as variable name because they are reserved words in Excel. It's confusing & a bad practice.

 

[Formula11]

OK no worries, thanks for the tip.

 

[Akuini]

@Formula11
I need to correct something:
this part:
With Rows(k + 1 & ":" & k + nr - 1)
should be:
With Rows(k + 1 & ":" & k + nr + 1)

 

[Akuini]

Sorry, it should be:
With Rows(k & ":" & k + nr)