Sort order with Blanks

**DanBlum** · 07-22-2011, 06:40 PM

I am sorting a worksheet and finding that I am getting confusing (to me) results with respect to the treatment of blanks. In one workbook, an ascending order sort puts rows with blanks first and in another workbook, it puts them last. ( Both in an ascending sort) .

I have checked for leading blank characters that might be confusing things in the non-blank cells and find none. (Used Left( A1, 1) to have Excel tell me what the first character is. It is the character I see visibly. Eg if A1 contains "Dog" it returns "D".)

I have used IF (A1="",true ,false) to verify that the visibly blank cells are indeed blank.

I am doing the sort in a macro (recorded by excel), but get the same result when I do the sort directgly through the user interface.

The help text says that blanks always sort to the end, I am seeing blanks at the end when I sort descending but as stated above, in one case it is putting the blank rows first.

(The exact data in the two workbooks is not the same.)

Can anyone suggest what might be causing the blanks to sort first?

If requested I can supply the worksheet with sample data.

**shg** · 07-22-2011, 07:17 PM

=A1="" doesn't tell you that a cell is empty.

Try =ISBLANK("A1")

**jaslake** · 07-22-2011, 11:00 PM

Hi DanBlum

A workbook would be good

I can supply the worksheet with sample data

Include your code in the workbook

I am doing the sort in a macro (recorded by excel),

If you have more than one workbook, post them both WITH Code

The exact data in the two workbooks is not the same

**DanBlum** · 07-22-2011, 11:06 PM

Thank you. I did the =isblank(a1) and sure enough it came out FALSE. (to my surprise)

So, please continue to help me. The column of cells I am testing (or sorting) have their value set by the following sequence:

First they have an if test as follows: =if( condition, "", "unique test string"). This sets some of the cell to whatever "" is....which I thought was blank, but I apparently don't understand.

Then, via a macro, that column gets copied to the clipboard and paste values to a new column. It is this new column of cells from the paste values that I am having sort trouble with.

Under the true condition, I need to set the value to something the user sees as blank and which sorts at the end of other alphanumeric strings.