Sorting by Two Columns: Sort Blanks Last

[Saighead]

Hi,

Is there a way to force Excel to sort these two columns based on column A but starting with cells where column B is not empty?

Column A　　　　　　Column B
a　　　　　　　　　　　　　　9
b
c　　　　　　　　　　　　　　12
d
e　　　　　　　　　　　　　　7

should be sorted like this:

Column A　　　　　　Column B
a　　　　　　　　　　　　　　9
c　　　　　　　　　　　　　　12
e　　　　　　　　　　　　　　7
b
d

"Helper" columns aren't an option. The file is large enough already without adding anything else to it...

[PaulM100]

Home - Sort & Filter - Custom Sort - Sort byy column B in first dropdown - Largest to Smallest - Done

[Saighead]

Home - Sort & Filter - Custom Sort - Sort byy column B in first dropdown - Largest to Smallest - Done

No, that will get you this:

Column A　　　　　　Column B
c　　　　　　　　　　　　　　12
a　　　　　　　　　　　　　　9
e　　　　　　　　　　　　　　7
b
d

and it should be:

Column A　　　　　　Column B
a　　　　　　　　　　　　　　9
c　　　　　　　　　　　　　　12
e　　　　　　　　　　　　　　7
b
d

[PaulM100]

Select fisrt 2 columns - Press Ctrl+G - Special - Blanks - Ok. Add a colour or a font colour to them
Home - Sort & Filter - Custom Sort - Sort by column B in first dropdown - Filter by colour in second dropdown - Select the cell colour or font colour in 3rd dropdown - Finally select bottom

[Saighead]

Select fisrt 2 columns - Press Ctrl+G - Special - Blanks - Ok. Add a colour or a font colour to them
Home - Sort & Filter - Custom Sort - Sort by column B in first dropdown - Filter by colour in second dropdown - Select the cell colour or font colour in 3rd dropdown - Finally select bottom

Thanks, I've thought of that, too. But the thing is: I cannot modify the cells in ANY way. I was hoping for some trick with sorting that I somehow overlooked or - as a last resort - a VBA script...

[PaulM100]

Ok, here is a VBA solution:

Please Login or Register  to view this content.

[Saighead]

Ok, here is a VBA solution

Sorry, no again.
Your script does not sort entries in column A alphabetically for each case (first, where column B isn't empty, and then where it is...)