Clear unprotected cells on all worksheets

[PrimePorkchop]

I'm using the following code that will clear all unprotected cells on the *CURRENT* worksheet.

How can I modify this to clear all cells within specified worksheets? For example, if I wanted to clear on the following tabs only:

NBA
NFL
MLB
NHL

Please Login or Register  to view this content.

[AB33]

Untested

Please Login or Register  to view this content.

[PrimePorkchop]

It gives an error "1004"

Method 'Union' of "object '_global' failed

[PrimePorkchop]

But it actually worked on "NBA" - the first sheet -- the rest of them remain untouched?

[AB33]

It probably errors when the object is nothing. Just added a single line

Please Login or Register  to view this content.

[PrimePorkchop]

Interesting -- all the sheets are cleared in half a second, but it keeps "thinking" for about 20 seconds until it errors out w/ the same error mentioned above.

[Marc L]

A VBA demonstration for starters :

Sub Demo1() 
    Dim V, Rc As Range 
        Application.ScreenUpdating = False 
    For Each V In [{"NBA","NFL","MLB","NHL"}] 
    For Each Rc In Sheets(V).UsedRange 
        If Not (Rc.Locked Or Rc.HasFormula) Then Rc.ClearContents 
    Next Rc, V 
        Application.ScreenUpdating = True 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[PrimePorkchop]

Oops I spoke too soon - it's not clearing the other sheets like I thought -- it's still erroring out after sheet 1

[AB33]

I have only added two lines, otherwise it is your code.

Please attach the sample and see what is going on

[PrimePorkchop]

Marc L - yours seems like it's wanting to work right away - but it's erroring out because "we can't do that with a merged cell"

Is there a workaround to work with merged cells?

[Marc L]

Yes a workaround exists but we need your attachment : follow the top page yellow banner …

[Richard Buttrey]

One way

Please Login or Register  to view this content.

[rorya]

For AB33's code you need to set rClear to Nothing after clearing it. If you have merged cells, I suggest using .Value = vbnullstring rather than .ClearContents. Well, actually I suggest not using merged cells, but that's another issue.

[CheeseSandwich]

I was playing with this so i will share, my code is almost the same as AB33's so you can see where to empty the union range:

Please Login or Register  to view this content.

Could not agree more with rorya about avoiding merged cells, try using centre across selection in the formatting dialogue as an alternative.

[JEC.]

This method does not loop over every cell, and probably works with merged cells (but always avoid them)

Please Login or Register  to view this content.