Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Functions Undefined | Excel Worksheet Functions | |||
determine if sheet contains mergearea(s) | Excel Programming | |||
Undefined function | Excel Programming | |||
Undefined function | Excel Programming |