Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Delete contents of unlocked cells in another worksheet
I found some help regarding this as a post to someone elses question.
The solution deletes the formatting in the cells as well. I need to keep the formatting. I'm also having trouble running the macro from another worksheet. The macro is as follows... this is the macro..... Sub clear() Sheets("Payroll - Collections - Pledges").Select ActiveSheet.Unprotect Password:=Password Range("C1:AR142").Select <-RIGHT HERE IS WHERE IT HANGS! Count = 0 On Error Resume Next For Each RNG In Selection If RNG.Locked = False Then Count = Count + 1 If Count = 1 Then Set Unlocked = RNG If Count < 1 Then Set Unlocked = Union(Unlocked, RNG) End If Next RNG Unlocked.clear <-NEED FORMATTING TO STAY! ActiveSheet.Protect Password:=Password End Sub Thanks! |
#2
|
|||
|
|||
Unlocked.clearcontents
KFEagle wrote: I found some help regarding this as a post to someone elses question. The solution deletes the formatting in the cells as well. I need to keep the formatting. I'm also having trouble running the macro from another worksheet. The macro is as follows... this is the macro..... Sub clear() Sheets("Payroll - Collections - Pledges").Select ActiveSheet.Unprotect Password:=Password Range("C1:AR142").Select <-RIGHT HERE IS WHERE IT HANGS! Count = 0 On Error Resume Next For Each RNG In Selection If RNG.Locked = False Then Count = Count + 1 If Count = 1 Then Set Unlocked = RNG If Count < 1 Then Set Unlocked = Union(Unlocked, RNG) End If Next RNG Unlocked.clear <-NEED FORMATTING TO STAY! ActiveSheet.Protect Password:=Password End Sub Thanks! -- Dave Peterson |
#3
|
|||
|
|||
Thanks Dave, I figured that part out later inthe day. I'm still having
problems with the part about running the macro from another worksheet though. Any thoughts? "Dave Peterson" wrote: Unlocked.clearcontents KFEagle wrote: I found some help regarding this as a post to someone elses question. The solution deletes the formatting in the cells as well. I need to keep the formatting. I'm also having trouble running the macro from another worksheet. The macro is as follows... this is the macro..... Sub clear() Sheets("Payroll - Collections - Pledges").Select ActiveSheet.Unprotect Password:=Password Range("C1:AR142").Select <-RIGHT HERE IS WHERE IT HANGS! Count = 0 On Error Resume Next For Each RNG In Selection If RNG.Locked = False Then Count = Count + 1 If Count = 1 Then Set Unlocked = RNG If Count < 1 Then Set Unlocked = Union(Unlocked, RNG) End If Next RNG Unlocked.clear <-NEED FORMATTING TO STAY! ActiveSheet.Protect Password:=Password End Sub Thanks! -- Dave Peterson |
#4
|
|||
|
|||
Make sure the code is in a General module. (Do you have it behind a worksheet?)
But you could get rid of some .selects this way: Option Explicit Sub clear() Dim Rng As Range Dim myCount As Long Dim Unlocked As Range With Sheets("Payroll - Collections - Pledges") .Unprotect Password:="Password" myCount = 0 Set Unlocked = nothing For Each Rng In .Range("C1:AR142").Cells If Rng.Locked = False Then myCount = myCount + 1 If myCount = 1 Then Set Unlocked = Rng Else Set Unlocked = Union(Unlocked, Rng) End If End If Next Rng If Unlocked Is Nothing Then 'do nothing Else Unlocked.ClearContents End If .Protect Password:="Password" End With End Sub KFEagle wrote: Thanks Dave, I figured that part out later inthe day. I'm still having problems with the part about running the macro from another worksheet though. Any thoughts? "Dave Peterson" wrote: Unlocked.clearcontents KFEagle wrote: I found some help regarding this as a post to someone elses question. The solution deletes the formatting in the cells as well. I need to keep the formatting. I'm also having trouble running the macro from another worksheet. The macro is as follows... this is the macro..... Sub clear() Sheets("Payroll - Collections - Pledges").Select ActiveSheet.Unprotect Password:=Password Range("C1:AR142").Select <-RIGHT HERE IS WHERE IT HANGS! Count = 0 On Error Resume Next For Each RNG In Selection If RNG.Locked = False Then Count = Count + 1 If Count = 1 Then Set Unlocked = RNG If Count < 1 Then Set Unlocked = Union(Unlocked, RNG) End If Next RNG Unlocked.clear <-NEED FORMATTING TO STAY! ActiveSheet.Protect Password:=Password End Sub Thanks! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel how do I delete or change the name of a range of cells? | Excel Discussion (Misc queries) | |||
How to Delete Contents of D2 if B2 = "SD" | New Users to Excel | |||
How do I print a cell's contents as a footer? | Setting up and Configuration of Excel | |||
Delete - Shift Cells UP problem | Excel Discussion (Misc queries) | |||
How can I merge unlocked cells in a worksheet that has been protec | Excel Discussion (Misc queries) |