Quickly Delete Blank Rows From A Long List

1.     First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .

2.     Now, select the cells in one column from the top of your list to the bottom.

3.     Make sure that all the blank cells in this selected range are the rows you want to delete.

4.     Press the F5 key on your keyboard (or select Edit, Goto).

5.     Click the Special button.

6.     Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.

7. Now choose Edit, Delete, select the Entire Row option and click OK.

VBA Code

Sub DeleteBlankRows2()

'Deletes the entire row within the selection if _

 some of the cells WITHIN THE SELECTION contain no data.

On Error Resume Next

Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

On Error GoTo 0

End Sub

---

Sub DeleteBlankRows3()

'Deletes the entire row within the selection if _

the ENTIRE row contains no data.

Dim Rw As Range

If WorksheetFunction.CountA(Selection) = 0 Then

   MsgBox "No data found", vbOKOnly, "OzGrid.com"

   Exit Sub

End If

    With Application

        .Calculation = xlCalculationManual

        .ScreenUpdating = False

    Selection.SpecialCells(xlCellTypeBlanks).Select

        For Each Rw In Selection.Rows

            If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then

                Selection.EntireRow.Delete

            End If

        Next Rw

        .Calculation = xlCalculationAutomatic

        .ScreenUpdating = True

    End With

End Sub