How to clearcontents of a merged cells

Question

I am trying to clear contents from cells but some of them are merged so I am getting the error

1004 :"We cant do that to merged cells"

For l = 4 To 9
    If ws.Cells(j, l).Interior.ColorIndex = 19 Then
         ws.Range(j, l).ClearContents  'Error here
    End If
Next l

Another Try using .Cells still it returns error

    For l = 4 To 9
        If ws.Cells(j, l).Interior.ColorIndex = 19 Then
             ws.Cells(j, l).ClearContents  'Error here
        End If
    Next l

You'll have to loop through each cell. Merged cells are the devil's work. — SierraOscar, Oct 16 '15 at 11:28

Rory · Accepted Answer · 2015-10-16T12:12:31.867

21

You need Cells not Range:

ws.Cells(j, l).ClearContents

Oops - forgot about the merged bit:

        If Cells(j, l).MergeCells Then
            Cells(j, l).MergeArea.ClearContents
        Else
            Cells(j, l).ClearContents
        End If

edited Oct 16 '15 at 12:12

answered Oct 16 '15 at 12:08

Rory

32,730
5
32
35

initially it was this way then I changed to Range to see if it works then also it didn't work – Stupid_Intern Oct 16 '15 at 12:11
@GordonBell no it doesn't. That's not valid `Worksheet.Range` syntax. – Rory May 20 '19 at 15:38
2

Ah, my mistake, ws.Range("NamedRange").MergeArea.ClearContents works. – Gordon Bell May 20 '19 at 22:14

score 3 · Answer 2 · answered Feb 25 '21 at 04:25

3

Try

Range("A1:A20").Value = ""

No matter cells are merged or not this will work everytime.

answered Feb 25 '21 at 04:25

hdd

45
7

score 0 · Answer 3 · answered Dec 23 '21 at 07:24

0

For l = 4 To 9
    If ws.Cells(1, l).Interior.ColorIndex = 19 Then
        ws.Cells(1, l).UnMerge
        ws.Cells(1, l).ClearContents
    End If
Next l

You can use unmerge method before clear.

answered Dec 23 '21 at 07:24

Beykan Gozumogullari

1
1

the UnMerge could be unwanted, and is not mentioned in the question (it could be added after the answer as "additional information", that would be a nice touch!). The For loop and the If condition are not related to the issue. When answering it's better to focus on the OP needs! – Rafiki Dec 23 '21 at 08:20

score 0 · Answer 4 · answered Sep 21 '22 at 23:01

0

I would do [J4].value = 0 then format cell ,the merge cell, Custom to 0;;0

answered Sep 21 '22 at 23:01

Matthew Little

1

score 0 · Answer 5 · answered Jul 12 '23 at 09:47

0

Range("i" & (5) & ":j" & (5)).ClearContents this worked for me

I merged the cell i5 with cell j5 and the original code was Range("i5").ClearContents that doesn't work

answered Jul 12 '23 at 09:47

NG83

17
4

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 12 '23 at 12:41

Vanda Ros · Answer 6 · 2017-08-04T14:33:52.677

One more thing you create macro and use shortcut key (ctrl + m) so you can select cells that you want merge and clear also.here is this code:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+m
'
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        If .MergeCells = True Then
            .MergeCells = False
        Else
            .MergeCells = True
        End If
    End With
End Sub

score -1 · Answer 7 · answered Oct 11 '19 at 16:56

-1

Try

ws.Range(j, l).Select
Selection.ClearContents

Worked for me.

answered Oct 11 '19 at 16:56

Luiz Silva

7
1

score -1 · Answer 8 · answered Mar 29 '20 at 13:57

-1

If Sheet Name.Range("cell no").MergeCells Then Sheet Name.Range("cell no").MergeArea.ClearContents Else Sheet Name.Range("cell no").ClearContents End If

answered Mar 29 '20 at 13:57

Shuva

11

score -1 · Answer 9 · answered Aug 03 '20 at 18:24

-1

I had the same issue and I could solve it with the following instruction:

ws.Range("D10:F1000") = vbNullString

Change the range and adapt it to your problem. It supports merge cells in the range area.

answered Aug 03 '20 at 18:24

Dorian Oria

119
10

score -3 · Answer 10 · answered Oct 16 '15 at 11:30

-3

Try using

ws.Range(j,l).Clearcontents

answered Oct 16 '15 at 11:30

Stian

187
4
12

Oh, I see what you have done now. good update. Problem is probably that you are asking excel to clear the contents of a single cell in a merged cell, which I do not now how to solve. Sorry. – Stian Oct 16 '15 at 12:18

How to clearcontents of a merged cells

10 Answers10

Linked