How to Find Merged Cells in Excel

Merged cells are typically so modest in appearance that finding them one by one can be challenging. It’s even more difficult when you’re dealing with enormous datasets with several cases of merged cells.

Using find and replace to find merged cells in excel.

We can clearly see from the above dataset that, cell A7 has been merged with A8. Same as cells B7 & B8. In addition to that, cells B1 & C1 have also been merged, and also cells B10 & C10.

Let’s see how we can use find and replace to find these merged cells in excel. Follow the steps below:

  • you can open the ‘Find and Replace’ dialog box by pressing the CTRL+H keys on your keyboard. Similarly,
  • you can choose the range of cells where you wish to look for merged cells.
  • Select ‘Find and Select’ from the Home tab’s ‘Editing’ group.
  • From the selection menu, choose Replace.

Select Options from the ‘Find and Replace’ dialog box.

Now, next to the ‘Find What’ input field, click Format. The ‘Find Format’ dialog box will appear.

Check the “Merge Cells” option under the Text Control choices on the Alignment tab.

Click the OK button. Return to the ‘Find and Replace’ dialogue box by doing so.

Select ‘Find All’ from the drop-down menu. You’ll see a list of all the cells in your selected range that have been merged.

Select all of the listed cells by selecting the first list item and then selecting the last list item while holding down the SHIFT key. This will select all of your merged cells at the same time.

Close the ‘Find and Replace’ dialogue box by pressing the Close button.

The highlighted cells in grey are the merged cells on the spreadsheet.

To make it more visible, you can highlight the cells to make them more outstanding. The steps below will guide you to do that.

To do so, go to the Home tab and select one of the styles from the Styles group. Alternatively, choose any other cell color in which you want to highlight the merged cells.

Finding and highlighting all Merged Cells in your Worksheet with VBScript

This is a super-easy technique to programmatically detect and highlight/unmerge all the merged cells in your sheet using VBScript.

The code we’ll be utilizing is as follows:

As the active sheet, make sure the sheet you want to work on is the one you want to work on. The code above will cycle through each cell in the working area of your active sheet.

Wherever the MergeCells property is set to True, the cell is highlighted by setting the Style property to “Note.”

The steps to use the above VBA macro code are as follows:

  • Make sure you have the sheet you want to work on as the active sheet.
  • From the Developer Menu Ribbon, select Visual Basic.
  • Once your VBA window opens, Click Insert->Module. Now you can put your code in.
  • Copy and paste the above code into the module window
techpady virtual classroom

Digital School Academy

Join our telegram channel and benefit from our regular digital skills trainings and also become a tech guru with very simplified tech tutorials

How to unmerge all merged cells in your worksheet using VBAScript & the merge and center button

After you’ve identified all of the merged cells in your sheet, you can choose to unmerge them to make them easier to deal with.

METHOD 1

To do so, go to the Home menu and select the “Merge & Center” option. This will unmerge all of the cells at once.

METHOD 2

You can add an extra line after line 5 if you wish to unmerge all the combined cells in your sheet.

To run the code, press F5 on your computer or click the button in the top toolbar.

Spread the love

Similar Posts

Leave a Reply

Your email address will not be published.