Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Merged Cells Not Identically Sized

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How do I deal with the message - "operation requires merged cells to be identically sized?"

View Answers     

Similar Excel Tutorials

How to Arrange Data within Cells in Excel
In this tutorial I am going to look at cell alignment / arrangement. These features allow you to change how data lo ...
Dynamic Named Range in Excel
How to create a named range that expands automatically when a new value is added to the range. This is a great lit ...
Average Non-Contiguous Cells in Excel
How to average non-contiguous cells (cells that aren't next to each other) if those cells contain numbers and are ...
Link to Cells on Other Worksheets in Excel
It is very easy to link one cell to another cell in Microsoft Excel. The steps needed are listed below as well as p ...
Quickly Move Cells Around a worksheet in Excel Cut/Paste Trick
Moving Cells around a worksheet in Excel is fast an easy. Below I have a table of forenames, surnames and ages. As ...
Quickly Copy the Last Action to Multiple Cells in Excel
In the previous tutorial I talked about the Redo button in Excel and how using Ctrl + Y can be used to do the same ...

Helpful Excel Macros

Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Add an Input Message to a Cell - Message Appears When the Cell is Selected in Excel - Data Validation Macro
- Add data validation input messages to cells in Excel with this free Excel macro. These messages appear when a specific

Similar Topics

I've got a new problem. When I try to sort my list which has company, city
and state Excel won't sort it I keep getting the message that "this operation
requires that merged cells must be identically sized". What merged cells? I
did nothing that I know of to prompt this message. No matter what I try it
won't do it. This is Excel 97. Can someone tell me why this is happening?

When I try to sort my data, I get this error: "This operation requires the merged cells to be identically sized". Does anyone know the fix so the data can be sorted?

I was given a large spreadsheet (737 rows and 45 columns) to clean up before
transferring into a database file. Certain cells text, others numbers,
emails, etc. and some are color coded to identify a specific distribution

Whenever I try to sort the data, the following message shows up and it won't
allow me to sort. "The operation requires the merged cells to be identically

I have spent the better part of a day trying to find the merged cells, but
have failed. Is there some way to find merged cells with the sheet without
clicking on every cell individually? Is there some other way to accomplish
the task for sorting? Thank you.

Working on Excel 2008 for Mac

'Find/Replace' doesn't include 'Options' for merged cells. Trying to sort my worksheet, running into "Operation requires merged cells to be identically sized."

I am NOT the creator of the spreadsheet. Has 1900 rows, 28 columns. Have looked, can't find the cells. It's in two columns that have phone numbers entered in too many ways. I will eliminate the merged cells when I find them.

When I try to check/uncheck 'merge cells for these columns, get the message,"Cannot merge overlapping ranges."

Would appreciate your help. If you provide code, please tell me where to enter it as I'm not familiar with using code. Thank you

I have at least 4 columbs and an varable amount of rows to deal with.

Trying to sort my data by first columb only I get error message of:
requires the merged cells to be identically sized.

I then formatedd all rows heights to 14 and columd widths to 9.
Received same error message. Any insight please?

I am getting this error when I select a merged cell and an adjacent cell and try to drag them down the page (the lower right hand corner grab, to have it extend the formulas into the cells you drag over)

I only see the solution to remove the merged cells, but I cannot remove the merged cells, as it would disturb the formatting of the spreadsheet.

I can select the merged cell and drag it individually, but just not when I select the merged cell in addition to other cells. How do I go about fixing this, so I can select the merged cells and the adjacent non merged cells and drag down the page.


This error happens whenever you try to paste special -> values where your source has merged cells.

SP2 supposedly fixed this but it's a lie.

Instead, use the "Values and Number Formats" option in paste special.

Hello All,
I’m doing a simple copy and paste special values of a range from one sheet in one workbook to another sheet in a different workbook. The sheets are set up identically. If I do a regular copy paste the data drops, however I need to paste special values. When I do the Paste special values I get the following Pop Up Warning –

This operation requires the merged cells to be identically sized.

I couldn’t find anything about this and not sure why I’m getting it, but it stops me from pasting specials values.


Dear All,

I need help with my VBA code which is not working. I am trying to use the following VBA code to sort my database based on a particular column – i.e. column B:


Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Intersect(Target(1, 1), Range("A:L")) Is Nothing Then 
        Range("A:L").Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

My database has 12 columns (A to L). The first 5 rows take up the headings – with all the headings formatted the way we require them. This means that – for some of the row headings, some of the cells are merged; the heading cells are not all equal in size. However, from Row 6 – the row from which we start entering data, all the cells are of the same size.

We use Excel User form to enter data onto the database. This is working OK.

The problem is with the sorting of the database based on column B (which contains Names of members). The first data entry row is Row 6 and the cell containing the first name is B6. In order to run the sort code, I right-clicked the sheet name (at the VBA editor) and pasted the code. But it does not seem to work. At some point, it comes up with an error message as follows:

“This operation requires the merged cells to be identically sized)."

I need help on what I should do to get the code to work. We require the database heading rows (rows 1 to 5) to remain the way they are – formatted. Please is there anything I can do so that upon clicking the ‘Add Data’ command button on the user form, the data will be added to the database and the sort code will run automatically and sort the database based on column B (not minding the fact that the cells making up the headings in rows 1 to 5 are not identically sized.

I have attached an abridged copy of the database (zip file)

Thanks for your anticipated help.



I have two columns in my excel sheet namely 'Painting date' and 'Raw data'. Each cell in 'Painting date' column consist of 10 merged rows whereas 'Raw data' consists of single rows. The 'data' in 'Painting date' is unsorted'. I want to sort 'both' the columns as per 'Painting date' column. But the excel does not allow this and says ' The operation requires the merged cells to be identically sized'.

The only way around this is that i un-merge the 'Painting date' column into single row column and then do the sorting. But doing so manually takes lot of time and i have more than 1000 rows of data. Is there is any way i can un-merge the 'Painting date' column automatically/quickly. The format tool does not work.

'PLZ see the attached excel file for detail'

Thanks in advance


I am trying to download some data from the server in excel format which I was previously downloading in PDF format.

Appearance wise the data looks same in excel file as it was in PDF.

Since I want to sort and apply few calculations on it, I need to get rid of uneven spacing of rows and columns between the data and also the page break wordings which repeats every time.

Is there any short way in excel to clean the file so that the information appears without any gap in rows. (Currently the gaps are uneven like for example in some places there is one extra blank line and sometimes its two extra blank line and so on. Also the sort features does not work as the message says " this operation requires the merged cells to be identically sized" )

Any help greatly appreciated.


Ok all here is my macro so far..


  Sub FitImageToCell()
    Dim sPicture As String, pic As Picture, PicName As String
    PicName = ActiveSheet.Range("A9").Text
    Set pic = ActiveSheet.Pictures(PicName)
    With pic
      .Left = ActiveSheet.Cells(11, 22).Left
      .Top = ActiveSheet.Cells(11, 22).Top
      '.Height = ActiveSheet.Cells(11, 22).Height
      .Width = ActiveSheet.Cells(11, 22).Width
    End With
End Sub

It does place the image in the correct cell, and technical sizes it correctly, except the cell is a merged cell, but the pic is sized to the actual cell itself, not the merged cell.

Is there any way to modify the above code so that if my Cell V11 is a merged cell, that covers say 5 columns and 10 rows for example, the image would be sized appropriately?

So i have a set of merged cells i want to use to write a big note in. It's 4c x 23r in size, and the columns are expanded to 87 pixels.

I have wrap text turned on and it works ok for small to medium sized paragraphs. But if i try to keep writing and fill the box i can only ever get text to fill up to 19r then it cuts off the text and doesn't display anything beyond that.

To be clear, the text is entered in the merged cell, but the lower 4 rows of the merged cell always stay blank regardless of how much text i put in the merged cell. I have tried reproducing this on in a new file, it works, until i expand the column width to 87 pixels then the problem happens.



I have an excel sheet that has rows merged (ex. A3-A19 and A20-36 are merged but data from B3-36 are not merged). I need to be able to use the "insert/table" function for an add-in, but when doing so, it un-merged cells of A3-A19. Is there any way to lock in the merged feature?

The feature I need from this add-in is its ability to split/separate each unique data entry in that column into separate worksheets. If anyone knows another way to do this without un-merging the cells that would work as well.

The website for the add-in I am using is below.

Thanks for any advice!

Hi All,

I have learned that using merged cells is not a good idea with excel. But, my workbook has grown too big to undo the merged cells. I have a protected workbook with some cells locked, some unlocked. I want the unlocked cells to lock once data has been entered. When I use the following code on the un-merged cells, it works fine:

Please Login or Register  to view this content.

But If I make the range include merged cells (B5:G100), ie Range("A5:O100"), then it fails.

I saw in another thread someone locking merged cells for someone else probelm using this:

First check if the cell is merged, such as:
If cell.MergeCells = True

If it is, set a range object to the merged range, such as:
Set mergedRange = cell.MergeArea

Then simply set the locked status of the merged range object, such as:
mergedRange.Locked = True

Is there someone that could edit my code to combine the merged cell check?


I have a database, where in the first column some cells are merged (by
2, 3 or more) and some are not. I want to number them in order that
every single or "group-of-merged-cells" cell will have just one
number. Excel says that autonumbering could be applied only to cells
with the same size.

For example, first cell is single, 2nd + 3rd are merged, 4th is single
and 5th+6th+7th are merged. This way about 10000 rows in first column.
How to number them in order?

I have a VBA script to number them, but (in the example above) first
has number one, second has number two, BUT third (it is row number 4!)
has number 4 => they are numbered from the first cell of the merged
group. It is wrong, on the 4th row it has to be a "3".

Huh, apologise my poor english, I hope the problem is set clear ;o)

Thank you for answer,

I have cells in Row 4 merged in pairs. That is, N4:O4 are merged into 1 cell, P4:Q4 are merged into 1 cell, R4:S4 are merged into 1 cell, etc, and there are 55 such pairs up to DR4:DS4.

Cells in Row 7 are not merged in pairs.

I would like some way of doing a HLOOKUP across the 55 merged cells in Row 4 and return both values in the corresponding cells in Row 7 underneath the matching cell Row 4.

I hope this makes sense.

I am using Excel 2007 and will need idiot proof instructions about how to implement a VBA solution if this is the only possible way of doing what I need.

Thanks in advance for your consideration of my question. The other teachers and secretaries at my school will never know how much of a help you will have been!

If anyone is interested, I found a way to automatically adjust a row height based on merged cells text. Here's how I did it...

In a cell unmerged and on the same row as the merged cells, enter the formula = merged cells and set the formula cell to the same column width as the combined merged cells. For example: Merged cells are B2:D2, formula in F2 is "=B2". Then set column F width to match merged cells. So if the merged cells combined column width is 30, set column F width 30.

Then in the worksheet VBA, use this code. This will automatically adjust the row height after the text in cell B2 is entered.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 Then
End If
End Sub

Happy VBAing.

It is probably easier to explain how to recreate my problem...

Merge a cell to the cell below it. Now merge the cell below the merged
cell to the cell below it. Make one more merged cell. Now put text in
each one like this "excel", "hates", "me".Now click on a different cell
somewhere else on the page and go to menu Data --> Validation. Choose
"List" and then in the source click the little button to the right and
then highlight your three cells. Now you have a drop down menu in the
somewhere else cell. But when you go to the dropdown menu it has blank
spaces in between the entries (because of the merged rows). If you do
the same thing but the list is with cells that are not merged to the
rows below then it doesn't have the blank rows. If you are only dealing
with cells that are merged in twos it is okay because it is spaced all
pretty and everything. The problem is when the cells are not merged
evenly so everything looks uneven.

So any ideas on how to get rid of the apparent blank entries in the
drop down menu??


Hi all,
I have a workbook which imports data from an external source on opening. Every time it imports data, I get the following message box appearing:

"This operation will cause some merged cells to unmerge. Do you wish to continue?"

Does any one know how to stop this message from appearing?

I was thinking something along the lines of Application.DisplayAlerts = False, but I'm unsure on how to apply it.

Any help please?

I have some code that looks like:


ActiveCell.Resize(2, 1).Select
'ActiveCell.PasteSpecial (xlPasteValues)

It switches to workbook1 and selects the range. The range looks like :

b c d
-0.3 1

So its b c and d 3 columns merged into 1 cell.

Then it switches to workbook2 and attempts to paste them into 2 rows that are also 3 columns merged into 1 cell.

When I execute the code above I get an error that the operation requires the merged cells to be identical size. But they are. If I comment out the last line and manually press control V, it works fine.

Is this an excel vba problem or mine?

Hello All,

The issue that I am having is with merged cell data. What I am trying to do is check the value of the merged cell against a selection from an array and place the value into a cell.

What apparently occurs is that if the row that I am on is an even number (being as my rows start on an odd number); the reference returns empty. I have tested to confirm that this only affects even numbered rows.

My spread sheet has a 2 row merged cell in column 2, and the cell I am referencing from is on the second row of that merged cell.

Here is some code:


dim AmpC as Integer
AmpC = 2 'This is the column with the merged cell

'Please note that "r" is used as a variable to loop through the rows; 
'Also, please ignore the reference to ArrayName and RandomNumber

If ArrayName(RandomNumber, 1) = Sheets("Current Schedule").Cells(r, AmpC).Value Then
    'Do something
    'Do nothing
End If

So the issue only occurs when "r" is the second row used in the merged cell

Anyone see how I can still be able to reference the value contained in the merged cell? I would also like to note, that testing has proven that when "r" is the first row used in the merged cell, there is no issue with referencing it's value

Word document provide input data which consists of a varyable number of lines
ending with paragraph mark and manual line break. Attepmting to copying the
input data into a merged cell results in "Data on the clipboard is not the
same size and shape as the selected area. Do you want to paste the data
anyway?" On clicking "OK", the paste fails with an error message "cannot
change part of a merged cell".

Can the line endings (including paragraph character) be converted into
Alt-Enter which Excel supports for manually entered multi-line data in single
and merged cells?

Is there any reason why you cannot copy the values from a single cell and paste the values into 2 merged cells? I have a macro running that resets my sheet to its original state but it won't fill the info into the merged cells. The merged cells have a data validation drop down menu so I don't want to have to split the cells up and align the text to centre across selection.

Hello all!
I am VERY frustrated right now.
I have a spradsheet that I've formatted using merged cells. The spreadsheet has several columns. Into these columns I must enter raw data extracted from our SAP data base. The extraction worksheet has no merged cells. So bottom line, I have a column of say, 30 non-merged cells and I want to paste that column into my formatted spreadsheet that has 30 merged cells of thre cells a piece (90 cells total).
So far I have been unable to do this. Any help would be appreciated.

If I've attached it correctly, the sample attached may help clarify.