How to find a merged cell in a work sheet full of data?

You can select a small range and then format|Cells|alignment tab and look at the
Merge box. If it's clear, there are no merged cells in the selection.

If there are, you divide your range into two parts and look at each part. Keep
dividing until you find all the cells that are merged.

Or you could use a macro:

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

Jithu wrote:

How to find a merged cell in a work sheet full of data?

--

Dave Peterson

Hi Jithu,

I just had a little play, and this will work but could get
a bit tedious in a large sheet or if you are looking for
a lot of merged cells.

For what it's worth
Select A1 and then Ctrl+Shift+Down Arrow
If only column A is selected then there are no merged cells in A
Tap the up arrow, then the right arrow, then Ctrl+Shift+Down Arrow
If only column B is selected then there are no merged cells in B
Keep going until more than one column is highlighted, this will signify
a merged cell in that column.
Tap the up arrow, then hold down Shift and keep tapping the down
arrow, the selection will stay in one column until you reach the merged
cell.

Like I said a bit tedious but not so bad if you are just searching
for one rogue cell.

HTH
Martin


"Jithu" wrote in message
...
How to find a merged cell in a work sheet full of data?

And another one,

Tap F5
Click Special
Check Blanks
Click OK

Any highlighted cell that has a value in it should be
a merged cell.

HTH
Martin

Thanks a lot Dave. It saved one day of work for me

"Dave Peterson" wrote:

You can select a small range and then format|Cells|alignment tab and look at the
Merge box. If it's clear, there are no merged cells in the selection.

If there are, you divide your range into two parts and look at each part. Keep
dividing until you find all the cells that are merged.

Or you could use a macro:

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

Jithu wrote:

How to find a merged cell in a work sheet full of data?

--

Dave Peterson