Sub FindMergedAreas()
Dim d, myCell As Range
Set d = Nothing
For Each myCell In ActiveSheet.UsedRange
If myCell.MergeCells Then
If d Is Nothing Then
Set d = myCell.MergeArea
MsgBox "First merged area is " & myCell.MergeArea.Address
Else
If Intersect(d, myCell.MergeArea) Is Nothing Then
MsgBox "Another merged area is " & myCell.MergeArea.Address
End If
Set d = Union(d, myCell.MergeArea)
End If
End If
Next myCell
End Sub

Can anyone give me an alternative macro to the one above which would
select all merged areas of a sheet (instead of finding).
Thxs

Hi Al,



Can anyone give me an alternative macro to the one above which would
select all merged areas of a sheet (instead of finding).

What problem are you experiencing with the macro?



It is rarely necessary, or desirable, to make selections.That said, to
select the merged areas, try inserting:



If Not d Is Nothing Then d.Select



after:



Next myCell



Incidentally:



Dim d, myCell As Range



does not do what you may think. This dims d as a variant and dims myCell as
a range object. Try instead:



Dim d as Range, myCell as range





---
Regards,
Norman



"al" wrote in message
ups.com...
Sub FindMergedAreas()
Dim d, myCell As Range
Set d = Nothing
For Each myCell In ActiveSheet.UsedRange
If myCell.MergeCells Then
If d Is Nothing Then
Set d = myCell.MergeArea
MsgBox "First merged area is " & myCell.MergeArea.Address
Else
If Intersect(d, myCell.MergeArea) Is Nothing Then
MsgBox "Another merged area is " & myCell.MergeArea.Address
End If
Set d = Union(d, myCell.MergeArea)
End If
End If
Next myCell
End Sub

Can anyone give me an alternative macro to the one above which would
select all merged areas of a sheet (instead of finding).
Thxs

Norman,
Thxs a lot for your prompt reply - actually am not experiencing any
problem with the macro - but I only want to improve it by selecting the
merged cells & unmerging them if necessary.
Cheers!!



Norman Jones wrote:
Hi Al,



Can anyone give me an alternative macro to the one above which would
select all merged areas of a sheet (instead of finding).

What problem are you experiencing with the macro?



It is rarely necessary, or desirable, to make selections.That said, to
select the merged areas, try inserting:



If Not d Is Nothing Then d.Select



after:



Next myCell



Incidentally:



Dim d, myCell As Range



does not do what you may think. This dims d as a variant and dims myCell as
a range object. Try instead:



Dim d as Range, myCell as range





---
Regards,
Norman



"al" wrote in message
ups.com...
Sub FindMergedAreas()
Dim d, myCell As Range
Set d = Nothing
For Each myCell In ActiveSheet.UsedRange
If myCell.MergeCells Then
If d Is Nothing Then
Set d = myCell.MergeArea
MsgBox "First merged area is " & myCell.MergeArea.Address
Else
If Intersect(d, myCell.MergeArea) Is Nothing Then
MsgBox "Another merged area is " & myCell.MergeArea.Address
End If
Set d = Union(d, myCell.MergeArea)
End If
End If
Next myCell
End Sub

Can anyone give me an alternative macro to the one above which would
select all merged areas of a sheet (instead of finding).
Thxs

If you're trying to unmerge all the cells, you could just do:

ActiveSheet.UsedRange.Cells.MergeCells = False



al wrote:

Norman,
Thxs a lot for your prompt reply - actually am not experiencing any
problem with the macro - but I only want to improve it by selecting the
merged cells & unmerging them if necessary.
Cheers!!

Norman Jones wrote:
Hi Al,



Can anyone give me an alternative macro to the one above which would
select all merged areas of a sheet (instead of finding).

What problem are you experiencing with the macro?



It is rarely necessary, or desirable, to make selections.That said, to
select the merged areas, try inserting:



If Not d Is Nothing Then d.Select



after:



Next myCell



Incidentally:



Dim d, myCell As Range



does not do what you may think. This dims d as a variant and dims myCell as
a range object. Try instead:



Dim d as Range, myCell as range





---
Regards,
Norman



"al" wrote in message
ups.com...
Sub FindMergedAreas()
Dim d, myCell As Range
Set d = Nothing
For Each myCell In ActiveSheet.UsedRange
If myCell.MergeCells Then
If d Is Nothing Then
Set d = myCell.MergeArea
MsgBox "First merged area is " & myCell.MergeArea.Address
Else
If Intersect(d, myCell.MergeArea) Is Nothing Then
MsgBox "Another merged area is " & myCell.MergeArea.Address
End If
Set d = Union(d, myCell.MergeArea)
End If
End If
Next myCell
End Sub

Can anyone give me an alternative macro to the one above which would
select all merged areas of a sheet (instead of finding).
Thxs


--

Dave Peterson