Find the index of the next empty row inside of a loop ( VB Excel )

Question

I have an excel sheet full of data sections, each data section is separated by an empty row.

While I'm looping over each row of the worksheet, I need to find the index of the next blank row so I can know where the current data section ends & apply modifications to it before passing to the next data section.

Here is an example of my first loop (inside this loop I need to find the index of the next blank row):

Dim x As Integer
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row    

Range("A1").Select
For x = 1 To lastrow
    If Left(Cells(x, "A").Value, 8) = "!JOURNAL" And Not (IsEmpty(Cells(x, "H"))) Then
          
'''Here I need to add another loop to find the index of my next blank row please'''
            idxblankrow = Range(Cells(x, "A")).CurrentRegion.Row
            MsgBox "Idx blank row is " & idxblkrow
            Range(Cells(x + 2, "A"), Cells(idxblankrow - 1, "H")).Cut Range(Cells(x + 2, "B"), Cells(idxblankrow - 1, "I"))
            Range(Cells(x, "H")).Select
            Selection.Copy
            Range(Cells(x + 2, "A"), Cells(idxblankrow - 1, "A")).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next

Here is another failed attempt(the second nest For loop is what tries to search for the blank row):

Dim x As Integer
Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
 
   
For x = 1 To lastrow
    If Left(Cells(x, "A").Value, 8) = "!JOURNAL" And Not (IsEmpty(Cells(x, "H"))) Then
        For j = x To lastrow
            If IsEmpty(Cells(j, "A")) Then idxblankrow = Cells(j, "A").Row
            MsgBox "blank row " & idxblankrow
                Exit For
            End If
                        
        
        Range(Cells(x + 2, "A"), Cells(idxblankrow - 1, "H")).Cut Range(Cells(x + 2, "B"), Cells(idxblankrow - 1, "I"))
        Range(Cells(x, "H")).Select
        Selection.Copy
        Range(Cells(x + 2, "A"), Cells(idxblankrow - 1, "A")).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    End If
Next

Any kind of help would be appreciated!

Answer 1

Please, use the next adapted way. It does not select, it does not use clipboard:

For x = 1 To LastRow
    If left(cells(x, "A").Value, 8) = "!JOURNAL" And Not (IsEmpty(cells(x, "H"))) Then
          
            idxblankrow = cells(x, "A").End(xlDown).Row
            MsgBox "Idx blank row is " & idxblankrow 
            Range(cells(x + 2, "A"), cells(idxblankrow - 1, "H")).Cut cells(x + 2, "B")
            'copy the value from "H" on the whole A:A column portion:
            Range("A" & x & ":A" & idxblankrow - 1).Value = cells(x, "H").Value 'not using clipboard...
            
            Stop 'check when stopped here if it did what you need
                 'if so, please press F5 to continue and check again.
            
            'you probably need to increment x to continue iteration after the processed portion
            'something as:
            x = x + (idxblankrow - x) + 2 '???
        End If
Next x

You probably need now to increment x with the number of rows which have been processed, but you must explain in words what you try accomplishing. Guessing is not an appropriate way of working here...

Answer 2

All these answers could be much simpler. Consider this:

iNextBlankRow = Sheet1.Range("A" & iNextBlankRow & ":A50").SpecialCells(xlCellTypeBlanks).Cells(1, 1).Row

To demonstrate, run this macro:

Sub BlankRowTest()
    Dim iNextBlankRow As Long
    Dim r As Long
    
    iNextBlankRow = 1
    For r = 1 To 50
        If iNextBlankRow <= r Then iNextBlankRow = Sheet1.Range("A" & iNextBlankRow + 1 & ":A50").SpecialCells(xlCellTypeBlanks).Cells(1, 1).Row
        Debug.Print r, iNextBlankRow, "'" & Sheet1.Cells(r, 1).Value & "'"
    Next
End Sub

This code loops through the first 50 rows looking for the next blank row. When it finds it, it assigns it to the variable iNextBlankRow. We don't bother updating that until our current row (r) is greater than or equal to INextBlankRow. At that point we look again starting from the next row.

Answer 3

Use flags to identify the start and end of the group. This deals with multiple blank rows between groups.

Sub macro()

   Dim ws As Worksheet
   Dim lastrow As Long, i As Long, n As Long
   Dim x As Long, z As Long
   Dim bStart As Boolean, bEnd As Boolean
   
   Set ws = ThisWorkbook.Sheets("Sheet1")
   n = 0
   With ws
       lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
       For i = 1 To lastrow
       
            ' start of group
            If Len(.Cells(i, "A")) > 0 Then
                bStart = True
                n = n + 1
            End If
            
            ' end of group look ahead
            If Len(.Cells(i + 1, "A")) = 0 Then
                bEnd = bStart
            End If
            
            ' valid range
            If bStart And bEnd Then
                x = i - n + 1 ' first row of group
                MsgBox "Processing rows " & x & " to " & i
                
                If Left(.Cells(x, "A").Value, 8) = "!JOURNAL" _
                    And Not (IsEmpty(Cells(x, "H"))) Then
                        ' process rows x to i
                End If
                ' reset flags
                n = 0
                bStart = False
                bEnd = False
            End If
                    
        Next
    End With
End Sub

Answer 4

If I want to know if an entire row is empty, I just concatenate the whole row and check the length. If this is zero, then the row is blank. Else, it's not.

See following exemplary screenshot (only the fourth row is empty, which is seen in the fourth formula, giving zero as a result):