Sort but ignore cells with ""

[dvent]

Hi All,

I have a set of cells that I want to sort within a macro. I want these to be sorted ascending but some of the cells contain "" to enable me to have blank looking cells. When I sort the cells the ones with "" come before those beginnign with A. Is there a way to avoid this?

This is the code I have currently.

Range("B9:H43").Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Thanks
dvent

[rwgrietveld]

They come after the last ones, do they not?

A--qwerty
B--home
""--Friend
D--People

End up in

A--qwerty
B--home
D--People
""--Friend

[dvent]

it seems that if i enter ="" manually, these cells end up at the bottom.

but when "" is enterred into the cells by a previous macro, they stay at the top of the list. very strange?!

[rwgrietveld]

NOTE that ="" is not the same as a blank cell.

See this:
?ASC("=""")
61
?ASC("a")
97
?ASC("A")
65
?ASC("Z")
90
?ASC("z")
122

The ASCI code of a blank does not exists and therefore it is placed at the bottom.