Run-time error '1004' Application-defined or object-defined error

[sdkorin]

I'm having some VBA problems. I've used the below code many times before on other workbook (just changing the specific parameters for each sheet)without any problem. However now I'm begging the "Run-time error '1004' Application-defined or object-defined error" error for the "East Mezz Input" line each time the code runs on the following day (where is supposed to clear the content. Any ideas?

Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
       ws.Protect Password:="JOHNNEY!", UserInterFaceOnly:=True
    Next ws
End Sub
Private Sub Workbook_Activate()
If Sheets("Aux").Range("B2").Value < Date Then
    Sheets("East Mezz Input").Range("C7:C43,D7:D43,G6:G43,I7:I43,J7:J43,K6:K43,M6:M43,P7:P43,Q7:Q43,T6:T43,V7:V43,W7:W43,X6:X43,Z6:Z43,AC7:AC43,AD7:AD43,AG6:AG43,AI7:AI43,AJ7:AJ43,AK6:AK43,AM6:AM43,AP7:AP43,AQ7:AQ43,AT6:AT43,AV7:AV43,AW7:AW43,AX6:AX43,AZ6:AZ43,BC7:BC43,BD7:BD43,BG6:BG43,BI7:BI43,BJ7:BJ43,BK6:BK43,BM6:BM43,BP7:BP43,BQ7:BQ43,BT6:BT43,BV7:BV43,BW7:BW43,BX6:BX43,BZ6:BZ43") = ClearContents
    Sheets("Wait Times and Submissions").Range("C6:D16,I6:I9,J6:J9,J11:J13,J15:J20,T6:T9,U6:U9,U11:U13,U15:U20,N6:O17") = ClearContents
End If
Sheets("Aux").Range("B2").Value = Date
End Sub

 

[Trebor76]

Hi sdkorin,

I'm surprised your code has worked before as the syntax is actually wrong i.e. it should be .ClearContents not = ClearContents

That said I think there's just too many ranges in a single string as it just stops working after BG6:BG43. The following is one way around this which slightly slower (so slight it probably won't be noticed) will do the job:

Option Explicit
Sub Macro1()

    Dim varMyRange As Variant
    
    Application.ScreenUpdating = False
    
    For Each varMyRange In Split("C7:C43,D7:D43,G6:G43,I7:I43,J7:J43,K6:K43,M6:M43,P7:P43,Q7:Q43,T6:T43,V7:V43,W7:W43,X6:X43,Z6:Z43,AC7:AC43,AD7:AD43,AG6:AG43,AI7:AI43,AJ7:AJ43,AK6:AK43,AM6:AM43,AP7:AP43,AQ7:AQ43,AT6:AT43,AV7:AV43,AW7:AW43,AX6:AX43,AZ6:AZ43,BC7:BC43,BD7:BD43,BG6:BG43,BI7:BI43,BJ7:BJ43,BK6:BK43,BM6:BM43,BP7:BP43,BQ7:BQ43,BT6:BT43,BV7:BV43,BW7:BW43,BX6:BX43,BZ6:BZ43", ",")
        Sheets("East Mezz Input").Range(CStr(varMyRange)).ClearContents
    Next varMyRange
    
    Sheets("Wait Times and Submissions").Range("C6:D16,I6:I9,J6:J9,J11:J13,J15:J20,T6:T9,U6:U9,U11:U13,U15:U20,N6:O17").ClearContents
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert