1

We use an application that generates a financial overview in excel. (Using excel 2010 at the moment.) Because of some configuration in that application sometimes it generates 4 or 5 empty rows between values in excel where we would only want one single empty row. I can't use a macro because those excessive rows do not have the same location every time.

I'm looking to remove those excessive blank rows, but I don't want to remove them all. Single blank rows are fine and very much needed. Is there some formula that would help me remove the extra blank rows minus the one blank row that is supposed to be there?

Hope you guys can help me out.

1
  • "I can't use a macro because those excessive rows do not have the same location every time" Does this mean you don't want a VBA solution, or you do not know how to come up with a VBA solution? Because VBA is the only option I can think of. Feb 16, 2018 at 15:07

1 Answer 1

1

For your first criteria, you can count how many cells in each row have values using the worksheet function CountA().

Code Snippet:

If .CountA(ws.rows(r)) = 0

Then you only wanted to delete the row if there is more than one empty row in succession, so we add this to our If...Then statement. This checks the above row with r - 1:

... And .CountA(ws.Rows(r - 1)) = 0

This checks if the above row is blank; only if it is then it will add your row to a special range that will keep track of your row deletions: delRng.

Here's the entire VBA code:

Sub removeEmptySpaces()

    Dim r As Long, ws As Worksheet, delRng As Range
    Set ws = ThisWorkbook.Worksheets(1)

    With Application.WorksheetFunction
        For r = 2 To lastRow(ws)
            If .CountA(ws.Rows(r)) = 0 And .CountA(ws.Rows(r - 1)) = 0 Then
                If delRng Is Nothing Then
                    Set delRng = ws.Rows(r)
                Else
                    Set delRng = Union(ws.Rows(r), delRng)
                End If
            End If
        Next
    End With

    If Not delRng Is Nothing Then delRng.Delete

End Sub

Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
    With ws
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
End Function

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .