Clearing the Contents of Merged Cells

  • Thread starter Thread starter prizm1
  • Start date Start date
P

prizm1

How would I implement VBA to clear the contents of a range that contains
merged cells? The following code results in a runtime error '1004' :
"Cannot change part of a merged cell."

*****************************************************

If Union(ActiveCell, Range("ProbArea")).Address =
Range("ProbArea").Address Then

Ans = MsgBox("Do you want to CLEAR the contents of CURRENTLY
SELECTED CELL'S ROW?", vbYesNo)

If Ans = vbNo Then
Exit Sub
End If

If Ans = vbYes Then

Application.ScreenUpdating = False

ActiveCell.EntireRow.Select
Selection.Cells(1, 1).Select
ActiveCell.Offset(0, 0).ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
ActiveCell.Offset(0, 3).ClearContents
ActiveCell.Offset(0, 4).ClearContents
ActiveCell.Offset(0, 5).ClearContents
ActiveCell.Offset(0, 6).ClearContents

Application.ScreenUpdating = True

End If

*********************************************

Offset (0, 1), (0, 2), (0, 3) and (0, 4) are merged cells.
 
p,
'---------------------
Dim Ans As Long
If Union(ActiveCell, Range("ProbArea")).Address = Range("ProbArea").Address Then

Ans = MsgBox("Do you want to CLEAR the contents of CURRENTLY Selected CELL 'S ROW?", vbYesNo)

If Ans = vbNo Then
Exit Sub
Else
Application.ScreenUpdating = False
With ActiveCell.EntireRow
.UnMerge
.ClearContents
End With
Application.ScreenUpdating = True
End If
End If
'----------------------
Jim Cone
San Francisco, USA



"prizm1" <[email protected]>
wrote in message
How would I implement VBA to clear the contents of a range that contains
merged cells? The following code results in a runtime error '1004' :
"Cannot change part of a merged cell."
*****************************************************
If Union(ActiveCell, Range("ProbArea")).Address =
Range("ProbArea").Address Then

Ans = MsgBox("Do you want to CLEAR the contents of CURRENTLY
SELECTED CELL'S ROW?", vbYesNo)
If Ans = vbNo Then
Exit Sub
End If
If Ans = vbYes Then
Application.ScreenUpdating = False
ActiveCell.EntireRow.Select
Selection.Cells(1, 1).Select
ActiveCell.Offset(0, 0).ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
ActiveCell.Offset(0, 3).ClearContents
ActiveCell.Offset(0, 4).ClearContents
ActiveCell.Offset(0, 5).ClearContents
ActiveCell.Offset(0, 6).ClearContents
Application.ScreenUpdating = True
End If
*********************************************

Offset (0, 1), (0, 2), (0, 3) and (0, 4) are merged cells.
 
Activecell.value = ""
(drop the .clearcontents version)
will work.

As will:
activecell.mergarea.clearcontents

(add in your .offset() stuff)
 
Back
Top