Merged cells

[GARY]

When trying to sort a worksheet, a box is displayed saying "This
operation requires the merged cells to be identically sized".

(The current worksheet contains data copied from a number of other
worksheets)

In the current worksheet, how do I find which cell(s) are the merged
cells?

 

[Sandy Mann]

There may be better ways, especially in later versions in than my XL97 but
assuming that the last used row is Row 250 then you could use:

Sub Merger()
LastRow = 250
Application.ScreenUpdating = False
For x = 1 To Columns.Count
Columns(x).Select
If Selection.Columns.Count > 1 Then
For y = 1 To LastRow
Cells(y, x).Select
If Selection.Columns.Count > 1 Then
Application.ScreenUpdating = True
Cells(y, x).Select
MsgBox "Merged cell " & Cells(y,
x).Address
GoTo GetOut
Application.ScreenUpdating = False
End If
Next y
End If
Next x
GetOut:
Application.ScreenUpdating = True
End Sub

The code will stop after you dismiss the messagebox so that you can unmerge
the cells then run the code again.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Sub findmerge()
Dim r As Range
Dim rr As Range
For Each r In ActiveSheet.UsedRange
If r.MergeCells = True Then
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Next
If rr Is Nothing Then
Else
rr.Select
End If


This will find all merged cells in the worksheet.

 

[Sandy Mann]

Where have you been? I spent 2 hours trying to check for Merged Cells in
code because I did not that the keyword was MergeCells not MergedCells <g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

I didn't know either. I just merged two cells with the Recorder on and
looked at the result.

 

[Saruman]

Posted previously for Excel 2003

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