Empty unprotected cells in one commando

jjsanders

New Member
Joined
Oct 16, 2006
Messages
36
Hello all,

I have a protected sheet. The sheet contains also fields which are NOT protected. Is it possible to empty all the unprotected fields at once. Because now I do ite lik this:

Code:
Range("B2").Value = Empty
Range("G6").Value = Empty
Range("G7").Value = Empty
Range("B11").Value = Empty

Thanks in advance!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi,

Place this code into a module and run it.

Code:
Sub test()

Dim c As Range

For Each c In ActiveSheet.UsedRange
    If c.Locked = False Then
        c.Clear
    End If
Next c

End Sub

Hope it does the trick! :)
 

jjsanders

New Member
Joined
Oct 16, 2006
Messages
36
I tried it like this:

Code:
Private Sub CommandButton3_Click()
    'Range("B2:B10").Value = Empty
    'Range("B2").Value = Empty
    
    Dim c As Range

    For Each c In ActiveSheet.UsedRange
        If c.Locked = False Then
         c.Clear
        End If
    Next c
        
End Sub

It gives a error by c.Clear
Error defined bij object or application
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Gosh I don't know why that error is occuring. Where ar you putting the commad button? Works for me if I create macro in a module andthen assign te macro to a command button from the Forms menu.
 

jjsanders

New Member
Joined
Oct 16, 2006
Messages
36
I put the putton in my sheet and when i press that button i want to empty the unprotected cells in the same sheet.
 

jjsanders

New Member
Joined
Oct 16, 2006
Messages
36
i think the problem has to do with merged cells. They cause the error.
Can anyone help me out?
 

Forum statistics

Threads
1,136,520
Messages
5,676,340
Members
419,619
Latest member
jalme

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