Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Never mind...found a VBA solution! Phew.

    I inherited a huge spreadsheet (16K rows and about 20 cols) that I need to sort by last name.

    The sort option tells me that merged cells have to be identically sized. I didn't think there were any merged cells (certainly weren't supposed to be).
    Is there any way other than brute force looking through this huge sheet to find what the sort option thinks are merged cells?

    Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post
    You can do this without VBA.

    Open the Find and replace dialog (keyboard shortcut Control+F)
    Click on the Options button to show further options.
    Click on the Format button to the right of the "Find what" box.
    This opens the "Find Format" dialog, in that dialog go to the Alignment tab.
    Make sure that the "Wrap text" and "Shrink to fit" boxes are not selected, and the "Merge cells" box is selected.
    Click OK to return to the "Find and Replace" dialog.
    Now click "Find All", and the dialog will expand to show the cell references of the merged cells.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by TonyE View Post
    You can do this without VBA.
    But apparently you can't do anything with the found cells by clicking the Replace tab in the Find dialog and setting the desired Format to not merged. Or am I missing something?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by JohnBF View Post
    But apparently you can't do anything with the found cells by clicking the Replace tab in the Find dialog and setting the desired Format to not merged. Or am I missing something?
    As far as I know you cannot use the replace option to change the cells to not being merged, I think you need to manually select each set of merged cells and change them. At least the Find dialog is not modal so it makes it relatively easy to go through all of the merged cells and change them.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Using XL2003 one can:
    • Select all cells
    • Format cells (Ctrl + 1`)
    • Uncheck "Merge cells" (from grey to black to blank)
    Regards
    Don

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    Never mind...found a VBA solution! Phew.

    I inherited a huge spreadsheet (16K rows and about 20 cols) that I need to sort by last name.

    The sort option tells me that merged cells have to be identically sized. I didn't think there were any merged cells (certainly weren't supposed to be).
    Is there any way other than brute force looking through this huge sheet to find what the sort option thinks are merged cells?

    Thanks.
    Good morning

    it seems to be my ASAP recommendation morning, I promise I am not on commission (it's free any way). If you load this add in select the whole workbook and then select option 11. format there is an option to unmerge all cells. I find this really useful when I receive CSV reports from some bespoke software at work because it will also delete leading and trailing spaces etc.

    HTH
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by kweaver View Post
    The sort option tells me that merged cells have to be identically sized. I didn't think there were any merged cells (certainly weren't supposed to be).
    Is there any way other than brute force looking through this huge sheet to find what the sort option thinks are merged cells?
    And this sort of problem is why I loathe merged cell in Excel.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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