I got a spreadsheet from my boss today and put a filter on it. The problem is the filter is completely useless because the "merged cells need to be identically sized." What in the world does that mean? Merged cells? The help button says to "unmerge all the merged cells in the range" or "make each group of merged cells the same size as the largest group." I can find absolutely nothing in the spreadsheet that says what a merged cell is or how to "unmerge" a merged cell.

Thank you for reading this! All I want to do is put a list in alphabetical order. Why did they make it so hard to do that?

• 08-20-2011, 10:10 AM

  Re: This Operation Requires Merged Cells To Be Identically Sized Hello & Welcome to the Board,

  Yes merged cells are a bad thing in Excel. Without seeing your spreadsheet it is hard to say where the merge cells exists, but I found this which should help

  http://www.worldstart.com/find-merged-cells-in-excel/

  No that you found them...get rid of them.

  An alternative to merge cells:

  Let's say the merged cells span A1:B1 >> select those cells >> ctrl + 1 >> Alignment tab >> Horizontal: Center Across Selection >> Text Control: Uncheck the Merge cells checkbox Last edited by jeffreybrown; 08-20-2011 at 10:14 AM. HTH Regards, Jeff

  -
  • 06-13-2012, 11:35 PM
    
    Registered User
    
    -

    Re: This Operation Requires Merged Cells To Be Identically Sized

    Hi All, Such a simple solution here, I was worried that I was in for an hour of "Googling", and some serious hair pulling. Just select all cells in your sheet, by clicking on the top left corner of the sheet - once all cells are selected they will "turn grey". Then right click anywhere in the "greyed out area" and select "Format Cells" - from there select "Alignment Tab". Click on "Merge Cells" until the box is empty (e.g. unticked), you may have to tick twice - I did - as the program knew that some cells had been merged and others hand not! Finally click on "OK" and then perform your data sort as normal. I wasn't a member of this forum until making the humble discovery above, but I figure sometimes you have to give back. I am getting Run-time error '1004': This operation requires the merged cells to be identically sized. I am using VBA CODE where macro copied the selected range from current sheet and paste to last row on sheet"Week". Code is Range("a7:ak34").Copy Sheets("Week").Select Range("A65536").End(xlUp).Offset(5, 0).PasteSpecial xlPasteValues ActiveSheet.Paste Application.CutCopyMode = False Range("A1").Select Appreciate if somebody help me to solve this problem.

    Excel Facts

    Excel Can Read to You Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

    

    Michael M

    Well-known Member

    Joined Oct 27, 2005 Messages 21,835 Office Version
  • 365
  • 2019
  • 2013
  • 2007 Platform
  • Windows

    2

    The stuff you are copying contains some merged cells. Unmerge them and see if that solves the issue. regards Michael M

    Reg123

    New Member

    Joined May 28, 2009 Messages 29

    3

    Ty Michael for prompt reply. This solves the problem but I need some cells merged. Thanks once again

    

    Michael M

    Well-known Member

    Joined Oct 27, 2005 Messages 21,835 Office Version
  • 365
  • 2019
  • 2013
  • 2007 Platform
  • Windows

    4

    Ok Try using "Centre across selection" Select the cells that you want "merged", then Format / Cells / Alignment In the horizontal box Select "center Across Selection" This will do the same thing without merging. Merging should be avoided wherever possible.....although that comment might light a few fires. regards Michael M

    Reg123

    New Member

    Joined May 28, 2009 Messages 29

    5

    After removing cell merge Still same error. macro always stuck on below mentioned CODE line. Range("A65536").End(xlUp).Offset(5, 0).PasteSpecial xlPasteValues

    

    Colin Legg

    MrExcel MVP

    Joined Feb 28, 2008 Messages 3,503 Office Version
  • 365 Platform
  • Windows

  • 6

    Try this, replace the sheet name in red with the name of the worksheet you are copying the range from. This code does the same as all of the code you posted in

    1.