Undefined MergeArea when MergeCells is true
 

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(CopySheetWor ksheet.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(CopySheetWor ksheet.Cells(Target.Row,
Target.Column), CopySheetWorksheet.Cells(Target.Row + Target.Rows.count,
Target.Column + Target.Columns.count))
End If

Undefined MergeArea when MergeCells is true
 

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


"bstobart" wrote in message
...
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(CopySheetWor ksheet.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(CopySheetWor ksheet.Cells(Target.Row,
Target.Column), CopySheetWorksheet.Cells(Target.Row + Target.Rows.count,
Target.Column + Target.Columns.count))
End If