How do I sort rows in Excel 2010 to get blank rows on top?

I need to sort rows in a sheet such that one or more blank rows move to the top in a range.

e.g.

Row 1 - data1
Row 2 - data2
Row 3 - data3
Row 4 - blank
Row 5 - blank

If I highlight all 5 rows to sort, I need the blank rows to move to the top to become rows 1 and 2.

I can't seem to find it in options or in the Help section.  Been doing for years with an ancient version of Lotus 1-2-3 but an converting my sheets to Excel.
As far as blank rows in Excel are concerned the focus seems to be on deleting them.

All help welcome.
 

Question Info


Last updated October 19, 2018 Views 16,938 Applies to:
Answer

I have a workaround for this..

 

1. First Select your Cells (data1, data2, data3, blank, blank), and

 

Press Ctrl + G -> Special ->Choose Blanks -> Ok

 

2. Use Fill color to color the background of the blank cells

 

3. Finally Sort By Color, Done.

 

Later you can remove the color after Sorting.

 

Useful?

Bharath

13 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer

Hi,

 

Try this

 

1. Select A1:A6.  A1 has the heading.  Press Ctrl+H.  Leave the Find what box blanks and in the Replace with box, type 1E100.

2. Sort the range in ascending order

3. Now remove the 1E+100 values by Ctrl+H

 

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

3 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.