Delete Blank Rows in Excel [Quick Tip]

I was doing this with VBA, but this one is much easier and faster.

There is one thing you should be careful about. If there are some missing values in whichever column they will be deleted as well so the data will displace and probably would not match with another column(s).

Surprised, this works exactly the same in Mac Office 2008. Even the keystrokes are the same. Unbelievable. Thanks.

Another great tip, so simple. When I saw it, I thought “I bet that only works in 2007″ but it is exactly the same in 2003. You learn something every day.

Check out the VBA (Macro) approach from Chip Pearson
http://www.cpearson.com/Excel/deleting.htm#DeleteBlankRows

Copy/paste into module in your personal.xls workbook and make always available when working with Excel

I use this all day long

Regards,
w

Another way to select all the blank cells is to press Ctrl+F; delete all the text, if any, in the “Find what” field; press Alt+i (or click Find All), then press Ctr+A, and then press the Esc key (or click the Close button)… all the empty cells will now be selected… follow the remainder of the instructions from the blog.

Thanks PHD for this and other posts….helping make us take small steps to becoming excel mini-Gurus at our work places! Will certainly share this…

Now, how do you find and delete the cells that return the null string (”")?

This is a nice trick, but I’m too old to remember function keys and ctrl minus key combinations. So, I created another way by making a custom toolbar. By creating a custom toolbar with icons that activate these same commands.

I use Excel 2007 so these instructions only apply to that version. PHD can cover this in more detail (he probably already has).

To create a custom toolbar click on the Excel ball in the upper left side of the menu. Click the Excel Options button in the lower right side of the dialog box. Then click Customize (the 5th item down the menu list). No you have the available options for adding to your custom menu.

Change the Choose Commands from drop down list to “All Commands” then scroll down to the “Delete Sheet Rows” and add it to the list. Continue scrolling down till you find “Go To Special…..” and add that. Hit the OK button. You should now have a customized toolbar at your disposal to invoke the same commands PHD did with function keys and ctrl minus key combinations.

I do a lot of work with large tables that contain gaps. This is a very nice little trick Chandoo. Thanks for posting it.

@Chandoo and John

In Chandoo’s comment to John about selecting empty strings, his advice after clicking the “Find All” button was “Now, select all the matches from find list (use shift to select)”… you do not have to do the selections one-at-a-time… you can select all the found cells all at once by pressing Ctrl+A.

As Cornelius warns, Chandoo’s method works only if the non-blank rows are completely filled (that is, there are no blank cells in the rows that have data in them). I would also note that cells with formulas evaluating to the empty string are considered non-blank and will not be deleted; so, like Cornelius’ warning, these cells will not be deleted while the blank (non-formula) cells next to them will. With these warnings in mind, and for those who are interested in such things, the VB equivalent to Chandoo’s method is a simple one-liner…

Sub DeleteTheBlankRows()
Cells.SpecialCells(xlCellTypeBlanks).Delete xlUp
End Sub

@Winston… I think the following, much shorter, macro does the same thing as Chip Pearson’s code does…
.
Sub DeleteBlankRows()
Dim X As Long, U As Range
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With ActiveSheet
For X = 1 To .UsedRange.Rows.Count
If .UsedRange.Rows(X).SpecialCells(xlCellTypeBlanks). _
Count = .UsedRange.Columns.Count Then
If U Is Nothing Then
Set U = Rows(X)
Else
Set U = Union(U, Rows(X))
End If
If U.Areas.Count > 100 Then
U.Delete xlShiftUp
Set U = Nothing
End If
End If
Next
End With
U.Delete xlUp
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

@Winston… I copied the wrong code in my previous post; there is a single line that is not correct, but I am posting all the code to make it easier to copy/paste it…
.
Sub DeleteBlankRows()
Dim X As Long, U As Range
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With ActiveSheet
For X = 1 To .UsedRange.Rows.Count
If .UsedRange.Rows(X).SpecialCells(xlCellTypeBlanks). _
Count = .UsedRange.Columns.Count Then
If U Is Nothing Then
Set U = Rows(X)
Else
Set U = Union(U, Rows(X))
End If
If U.Areas.Count > 100 Then
U.Delete xlShiftUp
Set U = Nothing
End If
End If
Next
End With
If Not U Is Nothing Then U.Delete xlShiftUp
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

When I want to remove blank rows of data. I just select the data and press sort Z>A. What are the limitations of that way?

@Wendell… That will work, of course, but it does have the side effect of leaving the data “out of order” which may or may not be a problem. If the existing data is in order with internal blank lines, then if there is a naturally sequential column (such as an “data entered date” or sequential “order number”), the data can be resorted on this column to put the data back into its original order. Otherwise, you can create a sequentially indexed column, sort the data Z-to-A according to one of the existing columns, delete the index numbers next to the blank rows them, and then sort the data A-to-Z on the index column to put the data back in the original order; but, of course, this involves (a small amount of) extra work to do.

I have been inserting a column and using edit, fill, series to get sequence numbers. Then sorting on the column containing blanks and deleting rows. It is quite fast but Chandoo’s idea sounds simpler.

When I know I am going to do a lot of crazy sorting I sometimes add a sequence column just to get back to my original order.

Many great tips – thank you!