Delete Empty Rows Quick?

Copper Contributor

I have a worksheet with thousands of rows and a blank row between each row with data.  Is there a way to remove all those blank rows quickly?  I need to sort by a column and I can't right now.  I assume that is because of the blank rows.

 

6 Replies

@LisaR1959 

Select the entire sheet (or its used range), then use the Tab key to move to the column you want to sort on, if necessary. You should then be able to sort the sheet, causing the empty rows to move to the bottom.

@LisaR1959 

If without shortcuts - select any column within range, on ribbon Find&Select->Go to Special->Blanks->Ok

All blank cells will be selected. With that Cells->Delete->Delete Sheet Rows

Thank You!!
Just what I needed!!!

@medstan , you are welcome

@LisaR1959 

Old post I know but this answer may be useful to someone.

Easiest way:
Select all cells with data in them - exclude headers if you have them.
On the ribbon bar (Home tab), select Sort & Filter
Select Sort A to Z
Done!  All the blank rows have been moved below the data.

 

@JimGarrison 

With that you loss initial rows order. If do such way it's more reliable to add helper column with sequential numbers from top to down, sort on any other columns, remove rows with blanks from down, sort again on helper column, remove helper column.

IMHO, on the range F5->Special->Blanks->Delete Sheet rows not very complex.