I regularly use simple macros to delete blank rows in my excel spreadsheets and there are loads of examples on internet forums.
I’ve recently had to recreate these delete row macros to install on a new computer and thought it would be useful to keep them filed here on the blog.
This first macro will delete any row in which the cell in the selected column is blank. So in this example, with column B selected, rows 4 & 7 will be deleted.
'Deletes entire row within the selected column where cell within selection is blank
‘Select column then run the macro
On Error Resume Next
On Error GoTo 0
The following macro will only delete rows if the entire row is blank. Just select the area you want to evaluate and run the macro.
'Deletes the entire row within the selection provided the ENTIRE row is blank.
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
To use these macros just copy and paste them into a module within your personal macro workbook (press ALT F11 to open the VBA editor) which will make them available for use whenever excel is open. Sometimes the personal workbook doesn’t show up in the VBA editor and if this is the case I just record a new macro, select the option to save it in the personal workbook, make a few changes to a spreadsheet and stop recording. This will have the effect of creating a new module in personal.xls, along with a fairly useless macro that can now be deleted.