VBA // Protection.AllowEditRanges

[DonAndress]

Hello.

I'd like to delete all editable ranges in the workbook, so I have this simple code:

Sub Macro4()

Dim wks As Worksheet
Dim aer As AllowEditRange


For Each wks In Worksheets

    For Each aer In wks.Protection.AllowEditRanges
        
        wks.Activate
        aer.Delete
        
    Next aer
    
Next wks


End Sub

But it's stuck on the aer.Delete part giving me "application or object defined error".
I have all sheets unprotected.

Can someone help me fix it?

 

[Peter_SSs]

Try unprotecting the worksheet (& re-protecting if required) just inside the start (& end) of the wks loop.

 

[DonAndress]

Yeah that helped, thank you!

Although there is one problem with this code.
When I just run it, it goes smoothly, but when I go step by step (F8) I get "Method 'Delete' of object 'AllowEditRange' failed" when it passes wks.Activate and should go through aer.Delete.

Do you by any chance know why it happens?

 

[Peter_SSs]

No, and I haven't been able to reproduce what you are describing. Perhaps it relates to passwords and/or permissions that have been applied to the particular ranges. Or hidden sheets.

In any case, I'm not sure my previous suggestion should have made any difference as I had missed the following comment in your original post:

I have all sheets unprotected