Clear contents

[Bomba]

With this code (below) I am trying to clear contents of cell A6 (which is merged) from sheet "CHIT1&2" and an error is appearing saying:

Run-time error '1004':
We can't do that to a merged cell.

Now I even want to clear contents of cell A6 from sheets "CHIT3&4" and "CHIT5&6" where cell A6 is also merged in both sheets.
So in a few words, I want to modify this code to clear contents in cell A6 (which is merged) in these 3 sheets.

Code: Select all

Sub ModifyFilesinFolder()
'Change file path to suit
Const FilPath As String = "C:\Users\littl\Desktop\2. February 2019"
'Change file extension to suit
Const FilExt As String = ".xlsm"
Const ShtName As String = "CHIT1&2"
Dim FSO As Object, Fils As Object, Ct As Long
Set FSO = CreateObject("Scripting.Filesystemobject")
Set Fils = FSO.getfolder(FilPath).Files
For Each F In Fils
    If F.Name Like "#" & FilExt Or F.Name Like "##" & FilExt Then
        Application.ScreenUpdating = False
        With Workbooks.Open(FilPath & Application.PathSeparator & F.Name)
            .Sheets("CHIT1&2").Range("A6").ClearContents
            .Save
            .Close
            Ct = Ct + 1
        End With
    End If
Next F
Application.ScreenUpdating = True
MsgBox Ct & " All cleared"
End Sub

Thanks

[HansV]

Use

Code: Select all

            .Sheets("CHIT1&2").Range("A6").MergeArea.ClearContents

[Bomba]

Thanks Master.