Blank rows in your Excel spreadsheet are undesirable and can make it difficult to work with. Because of this, Excel provides many ways that we can locate and then delete the blank rows.
These techniques can include sorting the list to group the blank rows together, filtering for blanks and using Go To Special to locate blank cells for deleting.
However these can be time consuming and may not be the best approach for you. For example, sorting the list will change the order of the list. You may not want this.
By creating a macro to delete the blank rows of a spreadsheet this process will be effortless, and can also be performed by anyone when the macro is made available with a button. In this Excel Macros tutorial we show you how.
To create the delete blank rows in a spreadsheet macro;
- Open the Visual Basic Editor by pressing Alt + F11
- Insert a module by clicking the Insert menu and selecting Module
- Enter or copy and paste the code below into the window
In the code below comments are used (shown in green) to explain each section of the code.
Dim lastrow As Long
Dim ISEmpty As Long
‘Count how many records in the list. This is done so that the Do loop has a finish point.
lastrow = Application.CountA(Range(“A:A”))
‘Start at the top of the list
‘Loop until the end of the list
Do While ActiveCell.Row < lastrow
‘Assign number of non empty cells in the row
ISEmpty = Application.CountA(ActiveCell.EntireRow)
‘If ISEmpty = 0 then delete the row, if not move down a cell into the next row
If ISEmpty = 0 Then
If you enjoyed this Excel macros tutorial then check out our online Excel VBA course to fast track your macro writing skills.