How do I locate merged cells

[Guest]

I have a workbook of some 13 thousand rows - I've just tried to do a sort
only to find sort cannot function as there are 1 or more merged cells. Is
there anyway I can 'find' the merged cells rather than checking each row by
row?

 

[Dave Peterson]

If you want to unmerge the cells (without regard to where it is), you can select
the whole range (the entire worksheet???) and

Format|Cells|Alignment tab|uncheck Merge Cells

 

[Guest]

Sorry Dave should have made it a little clearer - I don't want to simply
'unmerge' the offending cells as I data will subseqently be out of line when
I subsequently sort.
Jan

 

[Dave Peterson]

Is a macro ok?

Option Explicit
Sub testme()

Dim rptWks As Worksheet
Dim wks As Worksheet
Dim oRow As Long
Dim iCtr As Long
Dim myCell As Range

Set wks = Worksheets("sheet1")

Set rptWks = Worksheets.Add
rptWks.Range("a1").Value = wks.Name

oRow = 1
For Each myCell In wks.UsedRange.Cells
If myCell.MergeArea.Cells.Count > 1 Then
If myCell.MergeArea.Cells(1).Address = myCell.Address Then
oRow = oRow + 1
rptWks.Cells(oRow, 1).Value = myCell.MergeArea.Address
End If
End If
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Saruman]

Try This:

Use CTRL - F to open a Find And Replace Dialog Box
Click the options button
Make sure that the Find What Box is empty
Click the Format Button
Go to the Alignment Tab
Remove ticks from Wrap Text and Shrink To Fit
They will be greyed out but a second click will remove the tick
Click the Merge Cells to remove the grey shadow and leave the tick in a
clear box
OK back to the Find And Replace Screen
Click Find All Button
All merged cells are shown in the lower pane
Click on each one to go to them one at a time

Hope this helps!! Enjoy