Posted on Leave a comment

How to delete empty rows

Use the following VB Macro to delete empty rows:

‘ Sub Purpose: Delect all blank ROWS within the active cell’s
‘ Used Range

Public Sub DeleteBlankRows()
Dim dbMaxRow As Double, dbMinRow As Double, i As Double
Dim dbMaxCol As Double
Dim rng As Range
On Error Resume Next
‘only look in used area of the worksheet where active cell is
Set rng = Selection.Parent.UsedRange
‘calculate area to be searched for blank rows
dbMaxRow = rng.Rows.Count ‘# of rows in used area
dbMinRow = rng.Cells(1, 1).Row ‘1st row in used area
dbMaxCol = rng.EntireColumn.Count ‘# of columns in used area
For i = dbMaxRow To dbMinRow Step -1
If IsError(rng.Cells(1, 1).Offset(i – 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count) Then
If rng.Cells(1, 1).Offset(i – 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
rng.Cells(1, 1).Offset(i – 1, 0).EntireRow.Delete
End If
End If
Next i
Set rng = Nothing
End Sub

Leave a Reply

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