Quirky Sorting (2003)

[Lou Sander]

I make a lot of tables of data, typically including a differently-formatted Header Row. There's usually some other stuff above the Header Row, sometimes separated from it by a blank row, sometimes not.

Normally I can sort the data by selecting a cell within the table and using the Sort dialog box or the easy A-Z or Z-A icons I've added to my toolbar. Sometimes I have to tell Excel that there's a Header Row, but often it seems to figure that out on its own. The sorting is typically perfect, quick, and easy.

But sometimes Excel gets confused about where my data is. No matter what I do, it wants to sort the whole worksheet, not just the data below the Header Row. This often (always?) happens when there's a group of merged cells somewhere above the Header Row. I get a message about merged cells, and the data won't sort. No matter what I do, I can't get Excel to return to just sorting my data instead of everything on the worksheet. I can unmerge the cells, insert a blank row above the Header Row, close the worksheet and reopen it, etc. Nothing seems to restore the "normal" sorting process.

Who's got a fix?

[sdckapr]

In my experience, when the range is determined is quirky, there is something quirky about the region which confuses excel. Without seeing an example it is difficult to troubleshoot.

In addition to a blank row above the header row, a blank column to the right of the data may also help. Ensuring the header row has no blanks, may help and even bolding the header row can help.

Steve

[wdwells]

[quote name='Lou Sander' post='782871' date='03-Jul-2009 09:39']I make a lot of tables of data, typically including a differently-formatted Header Row. There's usually some other stuff above the Header Row, sometimes separated from it by a blank row, sometimes not.

Normally I can sort the data by selecting a cell within the table and using the Sort dialog box or the easy A-Z or Z-A icons I've added to my toolbar. Sometimes I have to tell Excel that there's a Header Row, but often it seems to figure that out on its own. The sorting is typically perfect, quick, and easy.

But sometimes Excel gets confused about where my data is. No matter what I do, it wants to sort the whole worksheet, not just the data below the Header Row. This often (always?) happens when there's a group of merged cells somewhere above the Header Row. I get a message about merged cells, and the data won't sort. No matter what I do, I can't get Excel to return to just sorting my data instead of everything on the worksheet. I can unmerge the cells, insert a blank row above the Header Row, close the worksheet and reopen it, etc. Nothing seems to restore the "normal" sorting process.

Who's got a fix?[/quote]
I find it worthwhile to select the range which I want sorted.

[Lou Sander]

I fixed the several worksheets that were troubling me. I copied them for backup purposes, then just deleted all the rows above the Header Row. After determining that the altered worksheets sorted without problems, I inserted some rows above the Header row, copied the material from the backup worksheets, and pasted it into the newly inserted rows.

Everything works fine now. Thanks for the tips!