Specified Cell range .ClearContents Error

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
226
Office Version
  1. 365
Platform
  1. Windows
I have a piece of code that worked perfectly until recently and now it is throwing up an error. Here is the odd bit, i can F8 step through it and it goes through just fine but when i use the assigned macro button it gives me this error 50% of the time.

The only other addition to the work book as a whole is a new worksheet that does not use the sheet i am trying to macro. Is this purely a "too much going on" issue?
1657655926656.png


Hitting Debug Highlights this line
1657656121331.png


The only thing that i added to this macro was unprotecting and protecting Worksheets("AACT")

VBA Code:
Sub Filter_Data()
    Worksheets("Complaints").Unprotect Password:="Secret"
    Worksheets("AACT").Unprotect Password:="Secret"
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Complaints")
    Set desWS = Sheets("AACT")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastrowb = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    desWS.Range(Cells(2, 1), Cells(50, 49)).ClearContents
    With srcWS
        .Range("AV2:AV" & LastRow).Formula = "=IF(AND(D2=""0101-6"",M2>=TODAY()-7),""true"",IF(AND(D2=""0101-6"",ISBLANK(M2)),""true"",""false""))"
        .Cells(1).CurrentRegion.AutoFilter 48, "true"
        .Range("A2:AK" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Columns.AutoFit
        End With
        .Range("A1").AutoFilter
        .Columns("AV").ClearContents
    End With
    With desWS
        .Range(Cells(2, 27), Cells(50, 28)).ClearContents
        .Range("AA2:AA" & lastrowb).Formula = "=IF(ISBLANK(J2),"" "",IF(ISBLANK(P2),Today()-J2,P2-J2))"
        .Range("AB2:AB" & lastrowb).Formula = "=IF(ISBLANK(K2),"" "",IF(ISBLANK(S2),Today()-K2,S2-K2))"
    End With
    Application.ScreenUpdating = True
    Worksheets("Complaints").Protect Password:="Secret"
    Worksheets("AACT").Protect Password:="Secret"
    ThisWorkbook.Save
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Have you placed this in a sheet module, or a general module?
If it is in a sheet module, try moving it to a general module in the sheet, and try running it again.
 
Upvote 0
The sheet needs to be active before you can use the ClearContents command on it.
So add this before the line giving you the error.
VBA Code:
desWS.Activate
 
Upvote 0
I tried that and it worked. not sure why all of a sudden i needed to activate it but im not complaining

I might try it with With and End With so i dont have use .activate
 
Upvote 0
I tried that and it worked. not sure why all of a sudden i needed to activate it but im not complaining
If you are already on that sheet when it hits that line of code, then you do not need to activate it.
But if you are on a different sheet, then you do.
So your introduction of the new sheet may have changed that for you.
 
Upvote 0
I am thinking that might be it, it runs alot of formulas so i bet its quickly activating the other sheet when its recalculating? you HAVE to be on the target sheet that macro is running for because its a button.
 
Upvote 0
The sheet does not need to be active, you just need to properly qualify the Cells properties:

VBA Code:
desWS.Range(desWS.Cells(2, 1), desWS.Cells(50, 49)).ClearContents
 
Upvote 0
Solution
The sheet does not need to be active, you just need to properly qualify the Cells properties:

VBA Code:
desWS.Range(desWS.Cells(2, 1), desWS.Cells(50, 49)).ClearContents
Ah yes, I always forget that!
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top