How do I locate merged cells in my Excel worksheet?


K

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?
 
Ad

Advertisements

S

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.
 
K

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. :)
 
S

Sheeloo

Glad I could help.

Thanks for your feedback.

KarenB said:
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. :)
 
F

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.
 
G

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
 
Ad

Advertisements

F

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.
 
Ad

Advertisements

G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top