Undefined MergeArea when MergeCells is true

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bstobart - 14 Sep 2007 14:42 GMT

I'm getting an error message in the workbook_sheetchange event when I execute
the first copy command below after Deleting the contents of a merged range.
When Updating the contents of the same cell this command works. The
MergeArea seems to be undefined. I thought the MergeArea had to be defined
if MergeCells=true. What am I missing?

If Target.MergeCells Then
Target.MergeArea.Copy
Destination:=CopySheetWorksheet.Range(CopySheetWorksheet.Cells(Target.MergeArea.Row,
Target.MergeArea.Column), CopySheetWorksheet.Cells(Target.MergeArea.Row +
Target.MergeArea.Rows.count, Target.MergeArea.Column +
Target.MergeArea.Columns.count))
Else
Target.Copy
Destination:=CopySheetWorksheet.Range(CopySheetWorksheet.Cells(Target.Row,
Target.Column), CopySheetWorksheet.Cells(Target.Row + Target.Rows.count,
Target.Column + Target.Columns.count))
End If

Reply to this Message

Bernie Deitrick - 14 Sep 2007 16:57 GMT

It depends on how your Target is defined. If the Target encompasses the entire merged area, then it
doesn't have a mergearea property.

For example:

Dim Target As Range
Set Target = Range("C4")
MsgBox Target.MergeArea.Column
Set Target = Range("C4").MergeArea
MsgBox Target.Column

So you could use

If Target.MergeCells Then
On Error Resume Next
Set Target = Target.MergeArea
On Error GoTo 0
End If

Target.Copy _
Destination:=CopySheetWorksheet.Range( _
CopySheetWorksheet.Cells(Target.Row, Target.Column), _
CopySheetWorksheet.Cells(Target.Row + Target.Rows.Count, _
Target.Column + Target.Columns.Count))

HTH,
Bernie
MS Excel MVP

> I'm getting an error message in the workbook_sheetchange event when I execute
> the first copy command below after Deleting the contents of a merged range.
[quoted text clipped - 14 lines]
> Target.Column + Target.Columns.count))
> End If

Reply to this Message