Results 1 to 4 of 4
  • Thread Tools
  1. 4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    533
    Thanks
    52
    Thanked 5 Times in 4 Posts
    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?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    10,932
    Thanks
    11
    Thanked 280 Times in 274 Posts
    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

  4. Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,608
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Regards
    Don

  5. 4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    533
    Thanks
    52
    Thanked 5 Times in 4 Posts
    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!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •