How do I locate merged cells in my Excel worksheet?

[KarenB]

How do I locate merged cells in my Excel worksheet? I am receiving an error
messages when I try to sort the worksheet which tells me to either unmerge
all the merged cells or merge all cells to be the same size as the largest
merged cell. I there some way to identify the merged cells?

 

[Sheeloo]

Thanks to saruman576 at http://www.mrexcel.com/forum/showthread.php?t=74144

To find a merged cell in Excel 2003.

1. Open a Find and Replace Menu.
2. Next to the Find What Field is a Format Box, Click it.
3. This opens the Find Format Menu screen which looks very similar to the
Cell Format Tabs and screens
4. On the Alignment Tab, remove any ticks in the Wrap Text or Shrink to Fit
boxes.
5. Leave a tick in the Merge Cells Box and click the OK Button to return to
the Find and Replace screen.
6. Now click the Find All Button and ALL merged cells are shown in the lower
window and are selectable to jump to their location.

 

[KarenB]

Thank you very much! It worked like a charm and I will waste no more time
trying to find a solution via Excel Help. Thanks a bunch and have a great
day.

 

[Sheeloo]

Glad I could help.

Thanks for your feedback.

Thank you very much! It worked like a charm and I will waste no more time
trying to find a solution via Excel Help. Thanks a bunch and have a great
day.

 

[FA]

Is there a way to then display the merged cells in the excel file? I want to
be able to view them all at once, kind of like a filter for merged cells.

 

[Gord Dibben]

You want a list of all cells that are merged?

Sub listmerged()
Dim i As Long
Dim mycell As Range
Set NewWks = Worksheets.Add
For Each mycell In Sheets("Sheet1").UsedRange
If mycell.MergeCells = True Then
With NewWks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = mycell.Address
End With
End If
Next mycell
End Sub


Gord Dibben MS Excel MVP

 

[FA]

I want to be able to view the merged cells as they appear in my excel
spreadsheet so that I can easily make changes to them. I'd like to hide all
of the rows that do not contain merged cells essentially. I'd like to try and
avoid having to scroll through 600 or so rows to find only the ones with
merged cells in them, and hopefully reduce any chance of missing them.

 

[Gord Dibben]

Quick and dirty manually.

Follow Sheelo's method to find all merged cells.

With "Found" dialog still open hit CTRL + a to select all cells in that
dialog.

On Toolbar select the fillcolor icon and select a color.

All merged areas will be colored and easy to find.

This macro will do the same thing.

Sub colormerged()
Dim i As Long
Dim mycell As Range
For Each mycell In ActiveSheet.UsedRange
If mycell.MergeCells = True Then
mycell.Interior.ColorIndex = 3
End If
Next mycell
End Sub

Filtering just for merged cells is not something I will tackle right now.

Maybe some other time just for the exercise.


Gord