(Solved) – delete rows if there are 2 consecutive empty rows

(solved)-–-delete-rows-if-there-are-2-consecutive-empty-rows

what i want to do is to delete rows if there are 2 consecutive empty rows and also to have the empty rows between the header and the first set of data row to be deleted as well.This is my original
input and what i want to have is this. i have tried to find some codes here and there and come up with this code.

Sub Testing()
    Dim i As Long , lRow As Long
    Dim ws As Worksheet

    Set ws = Activesheet
    With ws
        With .Range("C:C")
            fr = .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlValues).row
            If fr > 2 Then
                .Rows("2:" & fr - 1).EntireRow.Delete
            End If
        End With
        i = 1
        For i = 1 To lRow
            If IsEmpty(Cells(i, 3)) And IsEmpty(Cells(i   1, 3)) Then
                .Rows(i).EntireRow.Delete
           End If
        Next i
    End With
End Sub

However, there are still some consecutive empty rows in the middle of the data set. I know that is because i am increasing i which will look at the next cell but i am not sure how to solve it. I am new to vba and even newer to SO posting so let me know if there is anything i am doing wrong and thank you for your help.

Leave a Reply

Your email address will not be published. Required fields are marked *