Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to clearcontents of a merged cells

Tags:

excel

vba

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 
like image 321
Stupid_Intern Avatar asked Oct 16 '15 11:10

Stupid_Intern


2 Answers

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
like image 146
Rory Avatar answered Nov 17 '22 03:11

Rory


Try

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

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

like image 45
hdd Avatar answered Nov 17 '22 02:11

hdd