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.

Excel Sorting Error due to Merged cells

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.
Options In Excel Find
  • 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.
Alignment Tab In Find format Option
  • Uncheck the “Wrap Text” and “Shrink to Fit” options. Check the “Merge Cells” Option and click ‘OK’.
Merged Cells Found
  • 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 Range
Dim sMsg As String
sMsg = ""
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
If sMsg = "" Then
sMsg = "Merged worksheet cells:" & vbCr
End If
sMsg = sMsg & Replace(c.Address, "$", "") & vbCr
End If
Next
If sMsg = "" Then
sMsg = "No Merged Cells Found."
End If
MsgBox sMsg
End Sub
  • To Run this macro press ‘F5’, it will list all the merged cells in a message box.
Find Merged Cells in Excel
  • 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 Range
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
c.Interior.ColorIndex = 28
End If
Next
End Sub
Macro 2 Screenshot
  • 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.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.
You can find Ankit on FaceBook or Google Plus

Speak Your Mind

*

Privacy Policy | Disclaimer | About