-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
VBA Clear unprotected cells in a protected worksheet
- Thread starter APML
- Start date
Here's one way. (Run the top sub, where you put the sheet's name in place of ActiveSheet.Name.)
VBA Code:
Sub Test__Clear_UnprotectedCells_In_Sheet()
Call Clear_UnprotectedCells_In_Sheet(ActiveSheet.Name)
End Sub
Sub Clear_UnprotectedCells_In_Sheet(sheetName As String)
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Sheets(sheetName).UsedRange
If cell.Locked = False Then cell.Value = ""
Next cell
Application.Calculation = xlCalculationAutomatic
End Sub
Upvote
0
Firstly, thx for your help, I really appreciate it, I'm a complete amateur when it comes to VBA.
Based on your answer, I think I need to give you a little more detail.
My sheet is called ASX. On the sheet, there are lots of different sections with unprotected cells.
I was going to have several macros. i.e even though say A3, D3, G3 and F1 are unprotected I may only want to clear A3 and D3 (with 1 macro)
and G3 and F1 with a separate macro.. Hope this makes sense.
I'm thinking that your VBA would clear all the unprotected cells on the sheet at once.
Upvote
0
Here's a modification.
The following will clear all unprotected cells in column A.
The following will clear all unprotected cells in column D.
Etc.
Assuming that you want to clear all unprotected cells in each of the 4 columns you mentioned,
VBA Code:
Sub Test__Clear_UnprotectedCells_In_Sheet()
Call Clear_UnprotectedCells_In_Sheet("ASX", "A")
End Sub
Sub Clear_UnprotectedCells_In_Sheet(sheetName As String, columnLetter As String)
Dim lastUsedRowNumber As Long
With Sheets(sheetName).UsedRange
lastUsedRowNumber = .Rows.Count + .Row - 1
End With
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Sheets(sheetName).Range(columnLetter & 1 & ":" & columnLetter & lastUsedRowNumber)
If cell.Locked = False Then cell.Value = ""
Next cell
Application.Calculation = xlCalculationAutomatic
End SubThe following will clear all unprotected cells in column A.
VBA Code:
Call Clear_UnprotectedCells_In_Sheet("ASX", "A")The following will clear all unprotected cells in column D.
VBA Code:
Call Clear_UnprotectedCells_In_Sheet("ASX", "D")Etc.
Assuming that you want to clear all unprotected cells in each of the 4 columns you mentioned,
VBA Code:
Sub Macro1()
Call Clear_UnprotectedCells_In_Sheet("ASX", "A")
Call Clear_UnprotectedCells_In_Sheet("ASX", "D")
End Sub
Sub Macro2()
Call Clear_UnprotectedCells_In_Sheet("ASX", "G")
Call Clear_UnprotectedCells_In_Sheet("ASX", "F")
End Sub
Last edited:
Upvote
0
Sorry, I don't think I explained myself very well but again I really appreciate the help.
So what I'm wanting is a macro that will clear the specified cells which won't necessarily be in any order.
i .e one macro could do cell a3, b9, k290 etc (they are all unprotected on a protected worksheet
another macro might do z12, s220, j90 etc
Upvote
0
So I've had a really good look at this and your approach will work if I use rows (instead of columns), can you pls rewrite using rows? As an example :
Macro 1 would clear all unprotected rows (on a protected sheet) from row 10 to row 20
Macro 2 would clear all unprotected rows (on a protected sheet) from row 21 to 30.
The sheet is called ASX
Upvote
0
Okay, I will modify the code to tackle rows instead of columns. But first see this, which I was actually ready to post in response to your previous post.
.
.
.
It sounds like, from a programming point of view, it doesn't matter the status of the cells (being protected or not), as both of the previous solutions will auto detect them and clear them.
Based on your previous request, it sounds like you don't want it to clear all unprotected cells, just a subset of them. And you know which they are? If so, here's a sub that does that. Just enter the cells in a comma list in the Test sub at the top.
.
.
.
It sounds like, from a programming point of view, it doesn't matter the status of the cells (being protected or not), as both of the previous solutions will auto detect them and clear them.
Based on your previous request, it sounds like you don't want it to clear all unprotected cells, just a subset of them. And you know which they are? If so, here's a sub that does that. Just enter the cells in a comma list in the Test sub at the top.
VBA Code:
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
Upvote
0
wow, that works brilliantly. thanks so much.
just one more question. I going to need about 30 of these's macros.. so do i just copy the same and modify macro name (something like clear 1, clear 2 etc) and cell references ?.. From my questions, you can probably tell I'm not so great at VBA
Upvote
0
Okay, here you go. (It's a little longer, as I made it more efficient.)
VBA Code:
Sub Test__Clear_UnprotectedCells_In_Sheet()
Call Clear_UnprotectedCells_In_Sheet("ASX", 10, 20)
End Sub
Sub Clear_UnprotectedCells_In_Sheet(sheetName As String, startRow As Long, endRow As Long)
On Error GoTo No_Cells_To_Clear
Dim lastUsedColumnNumber As Integer
With Sheets(sheetName).UsedRange
lastUsedColumnNumber = .Columns.Count + .Column - 1
End With
With Sheets(sheetName)
Dim rng As Range, cell As Range
'Get the first cell.
For Each cell In .Range(.Cells(startRow, 1), .Cells(endRow, lastUsedColumnNumber))
If cell.Locked = False Then
Set rng = cell
Exit For
End If
Next cell
'Get the remaining cells.
For Each cell In .Range(.Cells(startRow, 1), .Cells(endRow, lastUsedColumnNumber))
If cell.Locked = False Then Set rng = Union(rng, cell)
Next cell
'Clear the cells.
rng.Value = ""
End With
No_Cells_To_Clear:
End Sub
Upvote
0
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.)
VBA Code:
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("ASX", 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 SubEDIT: I just changed ActiveSheet.Name to "ASX".
Last edited:
Upvote
0
