This article covers how to delete empty or duplicate rows in Microsoft Excel. It was written by Chip Pearson and is extremely useful.

Article by Chip Pearson of Pearson Software Consulting

Often, people ask for a macro to delete all blank rows or all duplicate rows from a range of rows in a worksheet. This page has three macros, DeleteBlankRows, DeleteRowOnCell, and DeleteDuplicateRows, which will do this.

Remember, these macros delete entire rows from your worksheet. They do not delete individual cells.

Delete Blank Rows

This macro will delete all of the blank rows in the active worksheet or in the selection. If the
current selection covers more than one row, only blank rows in those rows will be deleted. Otherwise, all blank rows in the entire worksheet will be deleted. The entire row must be blank for the row to be deleted.

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
    Set Rng = Selection
Else
    Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
    If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
        Rng.Rows(R).EntireRow.Delete
    End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

DeleteRowOnCell

The macro DeleteBlankRows will delete a row if the entire row is blank. This macro will delete the entire row if a the cell in the specified column is blank. Only this column is checked. Other columns are ignored. This macro was suggested by Dana DeLouis, in the Excel programming newsgroup. Thanks, Dana !

Public Sub DeleteRowOnCell()

On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

End Sub

To use this macro, select a columnar range of cells, and then run the macro. If the cell in that column is blank, the entire row will be deleted. To process the entire column, click the column header to select the entire column.

DeleteDuplicateRows

This macro will delete duplicate rows in a range. To use, select a single-column range of cells, comprising the range of rows from which duplicates are to be deleted, e.g., C2:C99. To determine whether a row has duplicates, the values in the selected column are compared. Entire rows are not compared against one another. Only the selected column is used for comparison. When duplicate values are found in the active column, the first row remains, and all subsequent rows are deleted.

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
    Set Rng = Selection
Else
    Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
    V = Rng.Cells(r, 1).Value
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
        Rng.Rows(r).EntireRow.Delete
        N = N + 1
    End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Here's another procedure that may be useful. Suppose you have two columns of data -- column A containing some names, and column B containing some dates. If the data is grouped (not necessarily sorted) by column A (but not necessarily by column B), this code will delete the duplicates rows, but retaining the latest entry (by column B) of each name in column A.

Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1
    If Cells(RowNdx, "A").Value = Cells(RowNdx - 1, "A").Value Then
        If Cells(RowNdx, "B").Value <= Cells(RowNdx - 1, "B").Value Then
            Rows(RowNdx).Delete
        Else
            Rows(RowNdx - 1).Delete
        End If
    End If
Next RowNdx
End Sub

Other Excel formulas and VBA procedures for working with duplicate and unique entries in lists can be found on the following pages:

Preventing Duplicates On Entry
Duplicates
Deleting Rows