How to find out merge cells from unknown position

Dear Excalers,
I have a question that is in worksheet.
How can I find out merged cells from a sheet?
Best regards,
K.boy

Think the easiest way is to get rid of all the merge cells in the sheet. If
this is not a problem (the "total removal of all merge cells" part), then
you could try:

Press CTRL + A to select entire sheet
Click Format > Cells > Alignment tab
Click to *clear* the "Merge cells" checkbox
(may have to click a few times)
Click OK

And if the above is too drastic an action, think this previous post below by
Dave Peterson in .misc would prove to be of great help:

Dave also provides a Sub testme02() which will inform you on the location of
merged cells in the sheet via message boxes (one at a time) e.g.: 1st msg:
"found at D14 Of D14:E16", Answer Yes*, 2nd msg: "found at B21 Of B21:C23",
and so on, until all merge cells are found
*but make a note somewhere on the details first, before answering Yes

"Manually, you could divide and conquer. Select half the range, hit ctrl-1
(to show the Format|Cell dialog). Look at that Alignment tab and look at
the Merge cells box. If it's not checked, look in the other half. If it's a
black check mark, you found it. If it's a grey check mark, you're getting
warmer--it's in the selected range.

Here's one way via a macro that looks at all the cells
in the usedrange:

Option Explicit
Sub testme02()
Dim myCell As Range
Dim resp As Long

For Each myCell In ActiveSheet.UsedRange
If myCell.MergeCells = True Then
If myCell.Address = myCell.MergeArea.Cells(1, 1).Address Then
resp = MsgBox(Prompt:="found at: " _
& myCell.Address(0, 0) & " Of " _
& myCell.MergeArea.Address(0, 0), _
Title:="Continue Looking?", _
Buttons:=vbYesNo)
If resp = vbNo Then
Exit Sub
End If
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

Don Edmondson wrote:

> Is there any (easy) way of finding a cell or cells that
> have been merged?
> I am trying to sort a (very large) spreadsheet but can't
> because Excel "...requires the merged cells to be the same
> size..."
> I can always select all and remove the merge - but I am
> wondering why there are merged cells - hence the reason
> for trying to find them!
-------- end --------

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"K.boy" <[email protected]> wrote in message
news:[email protected]...
> Dear Excalers,
> I have a question that is in worksheet.
> How can I find out merged cells from a sheet?
> Best regards,
> K.boy
>
>

> Dave also provides a Sub testme02()
> which will inform you on the location of
> merged cells in the sheet via message boxes (one at a time) ...

Hi guys,

Just wondering if/how could Dave P's Sub testme02() be modified to output
all findings of the merged cells to a new sheet (in col A, say) instead of
via msg boxes ? Would be easier to refer ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

The follow on query to modify Dave's sub has been posted as a fresh post in
..programming ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----