If instead of returning a blank you returned a very "large" text
value, like "zzzzz", then this will be sorted to the bottom of the
list (well, above your true blanks, anyway). You can apply conditional
format to the cell such that if it contains "zzzzz" then colour it
white (so that it will appear to be blank), and if you are doing Paste
Special then click on Formats as well as Values to maintain this
colour.

Main drawback is if you are doing arithmetic on the cell or the range
- you will need to check for the cell possibly containing "zzzzz", but
then you might be checking for "" elsewhere anyway, so you can include
this check at the same time.

Hope this helps.

Pete

On Nov 26, 11:48 pm, SteveM wrote:
In this example assume:
A1 never had a value and is blank
Cell B1 contains the formula =if(a1=0,"",a1) and should result in ""

Rows D and E are as follows

Row num Value
1 widgit
2
3

If I manually Copy B1 and Paste values in E3(below Widget), then sort by row
E with header, I come up with the following result:

Row num Value
2
1 widgit
3

B1 should have a result of "", which I would think is the same value as a
blank cell, hoever the sort does not group these together (row 2 and 3).
Sort also puts the result of row 2 "" ahead of widget in this example.

This problem was identified in a much more complex and larger project. The
above example is simplified for explantion purposes. In the real project the
values from column E are put into a combo box after a sort, and all the
blanks come to the top. We could sort z to a , but most users expect a to z
sorts as would I.

Does anyone know how to paste a value that will sort the same as a blank cell?