Find Merged Cells in Excel

There are times when you try to sort a column in your spreadsheet and it throws an error saying that, “To do this, all merged cells should be of same size”. As you can clearly see 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 be an easy task if the column is small but if you have a huge column with hundreds of elements, then manually searching for merged cells can become a cumbersome task.

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

Method 1: To find Merged Cells in Excel:

Actually in excel there is no direct option to find out merged cells present in a sheet. But in this method we are going to use the excel find and replace method quite cleverly to do this task:

• First of all select the range where you want to find merged cells.
• Next press “Ctrl+F” from keyboard, this will open excel “Find” window.

• Click the ‘Options’ button, and then the ‘Format’ button.
• After clicking on the format button a “Find Format” window will open, navigate to the ‘Alignment’ Tab.

• Uncheck the “Wrap Text” and “Shrink to Fit” options. Check the “Merge Cells” Option and click ‘OK’.

• Now click the ‘Find All’ button, this will show you all the merged cells present in the specified range.

Method 2: Finding Merged Cells using Macro (Listing Address of Merged Cells):

If the first method hasn’t worked for you don’t worry, we can use a macro to find out the list of merged cells and then populate their addresses in a message box.

• To create a macro, press the “Alt+F11” keys, this will open the excel VBA editor.
• Next Click on ‘Insert’ button and select the option ‘Module’.
• Now paste the below code in the editor window.

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

• To Run this macro press ‘F5’, it will list all the merged cells in a message box.

• You can also copy the cells listed in the dialog by simply click on the message box and then pressing “Ctrl+C”.

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

If you didn’t liked the above method then we have another option. In this method we will find the merged cell in the active worksheet and then we will color them. This can help you to easily catch the merged cells as they will be of a different color.

• To write a macro press “Alt+F11” keys, then click on ‘Insert’ > ‘Module’.
• Paste the below mentioned code in the VBA editor.

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

• To run the macro simply press the ‘F5’ key. It will change the background colour of all the merged cells to “Aqua”. You can also change the colour by replacing the number 28 by some other number between 0 – 56.

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