If the order of the rows doesn't matter, the easiest approach is to highlight all the data and then sort by one of the columns. The empty rows will end up at the bottom of the worksheet, and your problem is solved.
If you can't change the order of the rows, you need to create a macro that loops through the rows and deletes the empty ones. This process is tricky, because while the macro is zapping the rows, the numbers of the remaining rows keep changing. The solution is to loop through the rows backwards, so that deleting a row doesn't affect the numbers of the other rows.

This macro does just that.

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

If you're using Excel 5 or Excel 95, select Insert--Macro--Module to insert a new Visual Basic module into your workbook, and enter the code shown. In Excel 97, select Tools--Macro--Visual Basic Editor (or press <Alt>-<F11>), then select Insert--Module and enter the macro.
To run the macro, first activate the worksheet that you want to clean up. Then choose Tools--Macro (in Excel 97 the command is Tools--Macro--Macros), and select DeleteEmptyRows from the Macro dialogue box. The empty rows will vanish before your eyes. You'll want to save your workbook before executing the macro, because the operation cannot be undone. I included a command that turns off screen updating for faster performance.
- John Walkenbach