CODEWITHSUNDEEP
excelvba
Chatrapal Singh Rathore
Chatrapal Singh Rathore

Reputation: 13

How to apply code, to merged cells, that produces Runtime-Error :"We can't do that to merged cells."

This is the design I want to use to provide data to a table below and vice-versa.

enter image description here

Sub Cont_New()
With Sheet1
 .Range("R33").Value = True 'Contact Load
 .Range("R32").Value = True 'New Contact to True
 .Range("D7,D9,D11,D13,D15,D17,D19,D27").ClearContents
 .Shapes("ExistContGrp").Visible = msoFalse
 .Shapes("NewContGrp").Visible = msoTrue
 .Range("R33").Value = False 'COntact Load to False
End With
End Sub

How can I solve this issue with the merged cells?

Runtime-Error :"We can't do that to merged cells."

Upvotes: 1

Views: 198

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Since it is a MIX of both kind of cells, either use 

Range("D7,D9,D11,D13,D15,D17,D19,D27").Value=""
'OR
Range("D7,D9,D11,D13,D15,D17,D19,D27").Value = vbNullString

OR loop though the range and check if the cell is merged. If it is merged then use .MergeArea.ClearContents else use .ClearContents as shown below.

Option Explicit

Sub Sample()
    Dim rng As Range
    Dim aCell As Range

    Set rng = Sheet1.Range("D7,D9,D11,D13,D15,D17,D19,D27")

    For Each aCell In rng
        If aCell.MergeCells Then
            aCell.MergeArea.ClearContents
        Else
            aCell.ClearContents
        End If
    Next aCell
End Sub

Upvotes: 1

Related Questions