- Go to Tools >> Macros >> Visual Basic Editor OR press Alt-F11.
- The Project Explorer should be open in the upper-left corner. If not press Ctrl-R.
- In the Project Explorer, double-click the worksheet name to add the macro only to a single worksheet. Double-click ThisWorkbook to have it available to all worksheets.
- Paste the code (option 1 or option 2) into the worksheet.
- Go to View >> Microsoft Excel OR press Alt-F11.
- Select column A.
- Go to Tools >> Macros >> Macros OR press Alt-F8.
- Highlight the DeleteRows macro and click Run.
- Wait as it process through each row. To make it faster, only highlight the rows you want checked.
OPTION 1Sub DeleteRows()
RowCount = Selection.Rows.Count
Application.ScreenUpdating = False
For i = 1 To RowCount
If ActiveCell.Value = "" Then
Application.ScreenUpdating = True
Option 1 seems a little easier, while option 2 is a little faster. Option 1 checks for row value (= ""), while option 2 counts the number of empty cells in to see if it is equal to 0. Option 2 also turns off calculations, which is where the real speed is gained.OPTION 2Sub DeleteRows()
Dim theRows As Range
.Calculation = xlCalculationManual
.ScreenUpdating = False
For Each Rw In Selection.Rows
If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
That's it, now you have a worksheet without empty rows.