Delete Rows in Excel if Completely Empty

Add to Favorites

This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, run the macro, and then will delete only rows that have no data.

This is a great macro if you receive data that has only some empty cells but the columns next to the empty cells are still important.

Where to install the macro:  Module

Delete Completely Blank Rows

Public Sub DeleteCompletelyBlankRows()
Dim R As Long
Dim C As Range
Dim N As Long
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

N = 0

For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R
EndMacro: Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub





Similar Content on TeachExcel
Delete Blank Rows in Excel
Macro: This is a macro which will delete blank rows in excel. This version will delete an entire ...
Delete Comments in Excel - Permanently Remove Specific Comments from Excel
Macro: Delete a comment in Excel with this macro. You can permanently remove or delete a specific...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Increment a Value Every X Number of Rows in Excel
Tutorial: How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial...
How to Add, Remove, and Rearrange Columns and Rows in Excel
Tutorial: Adding, removing and rearranging columns is as easy as resizing. You just select your colu...
Delete All Rows that Contain a Specific Value in Excel
Tutorial: Quickly find all rows in Excel that contain a certain value and then delete those rows. ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Delete Blank Rows in Excel
Macro: This is a macro which will delete blank rows in excel. This version will delete an entire ...
Delete Comments in Excel - Permanently Remove Specific Comments from Excel
Macro: Delete a comment in Excel with this macro. You can permanently remove or delete a specific...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Excel Forum