Delete Rows in Excel if Completely Empty

Add to Favorites
Author:

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





Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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 ...
Custom Delete Rows in Excel
Tutorial: How to custom delete multiple rows at once in Excel. This allows you to check for as many...
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...
Increment a Value Every X Number of Rows in Excel
Tutorial: (New Easier Version for Excel 2010 and Later) How to increment a value, number, percent, e...
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...
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...


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.

Tutorial Details
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 ...
Custom Delete Rows in Excel
Tutorial: How to custom delete multiple rows at once in Excel. This allows you to check for as many...
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...
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course