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
Firstly, thx for your help, I really appreciate it, I'm a complete amateur when it comes to VBA.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
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 Sub
Call Clear_UnprotectedCells_In_Sheet("ASX", "A")
Call Clear_UnprotectedCells_In_Sheet("ASX", "D")
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
Sorry, I don't think I explained myself very well but again I really appreciate the help.Here's a modification.
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 Sub
The 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
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 :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
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
wow, that works brilliantly. thanks so much.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.
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
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
You're very welcome, but see if the above works for you.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 ?
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 Sub