Sometimes you need to find merged cells in Excel. And here we have found 2 methods to find merged cells in Excel worksheet.
Merged cells will interrupt you if you need to use references in formulas or other places. In the image below, you can see that there are some merged cells in the worksheet. And you need to find those cells in case you will make mistakes in your other operations.
Therefore, it is essential for you to find the merged cells in Excel. Below are the two methods that can help you solve this problem.
Method 1: Use Find Feature
- Press the shortcut keys combo “Ctrl +F” on the keyboard.
- And then click the button “Options” in the window.
- Next, click the button “Format” in the window.
- Now in the “Find Format” window, choose the tab “Alignment”.
- And then check the option “Merge cells” in the “Text control” part.
- After that, click the button “OK” in the window.
- Now you will come back to the “Find and Replace” window. Here click the button “Find All”. Thus, all the merged cells will appear in the window.
- Here press the button “Shift”.
- Click the last cell in the list. Thus, you have selected all the cells in the worksheet.
- Close the window.
- And then you can make some marks. Here we fill those cells with a color to make them different from other cells.
Method 2: Use VBA Codes
Except for the above method, you can also use VBA code to find merged cells.
- Press the shortcut keys “Alt + F11” on the keyboard.
- And then right click in the VBA project area.
- Next move your cursor on the option “Insert”.
- In the sub menu, choose the option “Module”. Thus, you have inserted a module for this workbook.
- Now input the following VBA codes into the module:
Sub FindMerge()
Dim cel As Range
For Each cel In ActiveSheet.Range(“A1:G13”)
If cel.MergeCells = True Then
‘change the color to make it different
cel.Interior.Color = vbYellow
End If
Next cel
End Sub
Here we will find the merged cells and mark them with a color. In this example, we input the range A1:G13 according to the worksheet. You need to change it into your target range according to the actual worksheet.
- And then press the button F5 on the keyboard. Therefore, the merged cells will be marked with a different color.
You can see that this method will produce the same result as the previous one.
A Comparison between the Two Methods
In order to help you make a choice between the two methods, we have made the table below.
| Comparison | Use Find Feature | Use VBA Codes |
| Advantages | 1. If you are not familiar with VBA codes, you can use this method.
2. You can also change the criteria in the “Find and Replace” window to find merged cells in the whole workbook. |
1. You can find all the merged cells with just one click.
2. You can apply this code into other workbooks. The only thing you need to do is changing the target range. |
| Disadvantages | 1. The process in this method contains many steps. This will be time consuming if there are many merged cells.
2. When you need to find merged cells in other workbooks, you need to repeat those steps again. |
1. Using VBA codes will make the task more complicated.
2. If you need to find with more complex criteria, you need to change the codes. And the problems appear in the VBA codes will be annoying. |
Keep Your Excel Files in Order
If your Excel files are in a mess, you will meet with Excel corruption sooner or later. And at that moment, your work will all be delayed. You have to repair it before you can continue your work. Now you can choose our repair tool to repair xlsx problem. And the next time, don’t forget to arrange your data and keep the Excel files in a clear order.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx problem and outlook repair software products. For more information visit www.datanumen.com
1