How To Make All Merged Cells The Same Size

In that location are times when you attempt to sort a column in your spreadsheet and it throws an mistake saying that, "To do this, all merged cells should be of same size". Every bit yous can clearly meet that this issue is due to the presence of merged cells in the column to be sorted.

To resolve this issue you simply need to find the Merged Cells present in the range to be sorted. This can exist an easy chore if the column is pocket-size just if you have a huge column with hundreds of elements, and so manually searching for merged cells tin can become a cumbersome task.

Excel Sorting Error due to Merged cells

Few days dorsum I also faced the same upshot and this is the reason why today I am going to guide you lot on how to find or locate merged cells in excel.

Method 1: To discover Merged Cells in Excel:

Actually in excel at that place is no direct option to detect out merged cells present in a canvas. But in this method we are going to apply the excel find and supervene upon method quite cleverly to do this task:

Outset of all select the range where you want to find merged cells.
Next press "Ctrl+F" from keyboard, this volition open up excel "Find" window.

Options In Excel Find

Click the 'Options' button, and so the 'Format' button.
After clicking on the format button a "Find Format" window will open up, navigate to the 'Alignment' Tab.

Alignment Tab In Find format Option

Uncheck the "Wrap Text" and "Compress to Fit" options. Check the "Merge Cells" Choice and click 'OK'.

Merged Cells Found

Now click the 'Detect All' button, this will show you all the merged cells present in the specified range.

Method two: Finding Merged Cells using Macro (List Address of Merged Cells):

If the starting time method hasn't worked for y'all don't worry, we can utilise a macro to find out the list of merged cells and so populate their addresses in a message box.

To create a macro, printing the "Alt+F11" keys, this will open the excel VBA editor.
Next Click on 'Insert' button and select the selection 'Module'.
Now paste the beneath code in the editor window.

Sub FindAllMerged()Dim c As RangeDim sMsg Equally StringsMsg = ""For Each c In ActiveSheet.UsedRangeIf c.MergeCells ThenIf sMsg = "" ThensMsg = "Merged worksheet cells:" & vbCrEnd IfsMsg = sMsg & Supervene upon(c.Address, "$", "") & vbCrEnd IfNextIf sMsg = "" ThensMsg = "No Merged Cells Found."Terminate IfMsgBox sMsgEnd Sub

To Run this macro printing 'F5', it will list all the merged cells in a bulletin box.

Find Merged Cells in Excel

Y'all can also copy the cells listed in the dialog by simply click on the message box and so pressing "Ctrl+C".

Method three: Finding Merged Cells using another Macro (Shading the Merged Cells):

If you didn't liked the above method and so we have another pick. In this method we will notice the merged cell in the active worksheet then we will color them. This tin can help you to hands catch the merged cells equally they will be of a different color.

To write a macro press "Alt+F11" keys, then click on 'Insert' > 'Module'.
Paste the beneath mentioned code in the VBA editor.

Sub ColorMergedCells()Dim c Every bit RangeFor Each c In ActiveSheet.UsedRangeIf c.MergeCells Thenc.Interior.ColorIndex = 28End IfNextEnd Sub

Macro 2 Screenshot

To run the macro simply printing the 'F5' fundamental. Information technology will change the background colour of all the merged cells to "Aqua". You tin can also modify the colour past replacing the number 28 by another number between 0 – 56.

So, these were the methods to notice merged cells in excel.