VBA // Protection.AllowEditRanges

DonAndress

Active Member
Joined
Sep 25, 2011
Messages
356
Hello.

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

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?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Try unprotecting the worksheet (& re-protecting if required) just inside the start (& end) of the wks loop.
 

DonAndress

Active Member
Joined
Sep 25, 2011
Messages
356
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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
Top