VBA Clear unprotected cells in a protected worksheet

[APML]

Hi All, am I able to clear unprotected cells in a worksheet that is protected (..aka, the sheet is locked but some cells aren't protected, can I use a VBA to clear them?), if so how?

 

[APML]

You're very welcome, but see if the above works for you.

But if not . . .


You can just put them in one macro like this. (I assume this would help you to see what you're doing. Enter them "row by row", where VBA will combine all "rows" as one and do the clearing. Just fill in the stuff in between the two ---- lines. Copy/paste as many such "rows" as you need. But honor the way the commas are presented here.)

Sub Test__Clear_These_Cells()
Dim list As String
'--------------------------------------------
list = list & "," & "a3,b9,c19"
list = list & "," & "d13,e53,f39"
list = list & "," & "g33,h93,i3"
'--------------------------------------------
Call Clear_These_Cells([B]"ASX"[/B], Right(list, Len(list) - 1))
End Sub
Sub Clear_These_Cells(sheetName As String, listOfCells As String)
Dim s() As String, rng As Range, i As Long
s = Split(listOfCells, ",")
Set rng = Range(s(0))
For i = 0 To UBound(s)
    Set rng = Union(rng, Range(s(i)))
Next i
rng.Value = ""
End Sub

So I could use either one of the macros.. is one more efficient than the other or are they the same ? I'm thinking the second last one is probably better because I do have some merged cells.
Just to avoid confusion, I'm thinking this is probably better:

Sub Test__Clear_These_Cells()
Dim listOfCells As String
listOfCells = "a3,b9,c19"
Call Clear_These_Cells("ASX", listOfCells)
End Sub
Sub Clear_These_Cells(sheetName As String, listOfCells As String)
Dim s() As String, rng As Range, i As Long
s = Split(listOfCells, ",")
Set rng = Range(s(0))
For i = 0 To UBound(s)
Set rng = Union(rng, Range(s(i)))
Next i
rng.Value = ""
End Sub

 

[cmowla]

They are all pretty efficient (for how long it takes the code to run). But if you want to save yourself time, why not choose the "10 to 20 row version"? That is, if you know for certain that all of the unprotected cells in those rows need to be cleared. Because typing in row numbers is much less prone to human error than typing in cell addresses in a comma series!

EDIT:
And just know that if you were "freaked out" when you saw the solution in this post, the order in which you type the cell addresses does not matter.

 

[APML]

You have been a great help, I really appreciate it. I'm not so great at VBA so your help has saved me endless hours of torment
So thought I'd show you the final code, I've called it Clear_W1. There will probably be about 40 of these,
Am I right in saying that I need to copy this 40 times and name them separately from Clear_W1 right up to Clear_W40, and just change the ranges, or is there a better way?


Sub Clear_W1()
Dim listOfCells As String
Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32")
End Sub
Sub Clear_These_Cells(sheetName As String, listOfCells As String)
Dim s() As String, rng As Range, i As Long
s = Split(listOfCells, ",")
Set rng = Range(s(0))
For i = 0 To UBound(s)
Set rng = Union(rng, Range(s(i)))
Next i
rng.Value = ""
End Sub

 

[cmowla]

No, you just need to copy the line:

Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32")

40 times (in that same sub).

And you can remove the line:

Dim listOfCells As String

In the top sub, as you won't need it if you do this.

 

[APML]

No, you just need to copy the line:

Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32")

40 times (in that same sub).

And you can remove the line:

Dim listOfCells As String

In the top sub, as you won't need it if you do this.

how do I trigger the separate clears?

 

[cmowla]

how do I trigger the separate clears?

Sub Clear_W1()
Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32")
Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32")
Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32")
'.
'.
'.
Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32")
End Sub

If that's not what you mean, then:

Sub Clear_W1():Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32"):End Sub
Sub Clear_W2():Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32"):End Sub
Sub Clear_W3():Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32"):End Sub
Sub Clear_W4():Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32"):End Sub

 

[APML]

Hi, I'm back again and hoping you can help me.
Re the above post
I've gone through my sheet and named the ranges that need to be cleared. i.e

Named Range "C_1" refers to =Watch!XEX168,Watch!XFC168,Watch!XFD168,Watch!B168,Watch!C168,Watch!XEX171,Watch!XFA171,Watch!XFB171,Watch!XFC171,Watch!XFD171,Watch!A171,Watch!XEX174,Watch!C173,Watch!B175,Watch!C175,Watch!XFD184,Watch!B184,Watch!XFD187,Watch!B187,Watch!XEX189,Watch!XEX191,Watch!XEY191,Watch!XEZ191,Watch!XFA191,Watch!XFB191

Named Range "C_2" refers to
=Watch!H158,Watch!M158,Watch!N158,Watch!P158,Watch!Q158,Watch!H161,Watch!K161,Watch!L161,Watch!M161,Watch!N161,Watch!O161,Watch!H164,Watch!Q163,Watch!P165,Watch!Q165,Watch!N174,Watch!P174,Watch!N177,Watch!P177,Watch!H179,Watch!H181,Watch!I181,Watch!J181,Watch!K181,Watch!L181

And so on right up to "C_50"

So basically I have 50 sections in my sheet, and within those sections, I need to clear some cell (hence "C1 to C50)
I was going to use a form button within each section, to trigger the clear cells for that section.
i.e there will be 50 buttons, if I click on the button in say section 2 then that would clear "C_2" range, and so on

So I guess what I'm asking is how do I modify your VBA to do this .. I'm not very good at VBA but I'm slowly getting there
The code I was using is:

Sub Clear_W1()
Dim listOfCells As String
Call Clear_These_Cells("ASX_Data", "B9,G9,H9,J9,K9,B12,E12,F12,G12,H12,J12,K14,J16,K16,B15,H25,J25,H28,J28,C30,B32,C32,D32,E32,F32")
End Sub
Sub Clear_These_Cells(sheetName As String, listOfCells As String)
Dim s() As String, rng As Range, i As Long
s = Split(listOfCells, ",")
Set rng = Range(s(0))
For i = 0 To UBound(s)
Set rng = Union(rng, Range(s(i)))
Next i
rng.Value = ""
End Sub