VBA to clear multiple ranges from list of sheets

[y0rk1e72]

Let me explain
How do i get a macro/vba to look at a list of sheets in one workshee
and depending on the data to the right of the name clear the correc
range(s)
for example list would look lik
staff M5, D14:E1
manager D9:E39, G44:I4
director B44:E4

so using the above i'd need to look at the name, i.e staff and it woul
clear M5, followed by the range D14:E14 then would look and the nex
name which would be manager and clear D9:e39 followed by G44:I49. i
would then look at the next name in the list which using the above woul
be director

The sheets are hidden and have merged cells within the range to b
cleared

is this even possible
any help/pointers would be greatly appreciated

also posted o
http://tinyurl.com/a7aoz8
http://tinyurl.com/aofkj9
http://tinyurl.com/cbks2k

 

[isabelle]

hi,

i hope that this example will be useful,

Sub test()
Dim oSh As Worksheet, Rng As Ranges, i As Integer
Dim sSh()

sSh = Array("staff", "manager", "director")

For Each oSh In Worksheets
For i = LBound(sSh) To UBound(sSh)
str1 = Len(Application.Substitute(oSh.Name, sSh(i), ""))
str2 = Len(oSh.Name)
If str1 <> str2 Then
'MsgBox oSh.Name & " contains the word <" & sSh(i) & ">" 'test
Select Case i
Case 0: Set Rng = Union(Range("M5"), Range("D14:E14"))
'staff sheet
Case 1: Set Rng = Union(Range("D9:E39"), Range("G44:I49"))
'manager sheet
Case 2: Set Rng = Range("B44:E49")
'director sheet
End Select
oSh.Range(Rng.Address).ClearContents
End If
Next i
Next
Set Rrg = Nothing
End Sub

 

[isabelle]

this code will be more efficient if the number of sheets and cells is big

Sub test()
Dim oSh As Worksheet, Rng As Range, i As Integer, str1 As String, str2
As String
Dim sSh()
sSh = Array("staff", "manager", "director")

For Each oSh In Worksheets
str1 = Len(oSh.Name)
For i = LBound(sSh) To UBound(sSh)
str2 = Len(Application.Substitute(oSh.Name, sSh(i), ""))
If str1 <> str2 Then
' MsgBox oSh.Name & " contains the word <" & sSh(i) & ">" 'test
Select Case i
Case 0: Set Rng = Union(Range("M5"), Range("D14:E14"))
'staff sheet
Case 1: Set Rng = Union(Range("D9:E39"), Range("G44:I49"))
'manager sheet
Case 2: Set Rng = Range("B44:E49")
'director sheet
End Select
oSh.Range(Rng.Address).ClearContents
Set Rrg = Nothing
End If
Next i
Next
End Sub

 

[y0rk1e72]

this code will be more efficient if the number of sheets and cells i
big

Sub test()
Dim oSh As Worksheet, Rng As Range, i As Integer, str1 As String, str2
As String
Dim sSh()
sSh = Array("staff", "manager", "director")

For Each oSh In Worksheets
str1 = Len(oSh.Name)
For i = LBound(sSh) To UBound(sSh)
str2 = Len(Application.Substitute(oSh.Name, sSh(i), ""))
If str1 <> str2 Then
' MsgBox oSh.Name & " contains the word <" & sSh(i) & ">" 'test
Select Case i
Case 0: Set Rng = Union(Range("M5"), Range("D14:E14"))
'staff sheet
Case 1: Set Rng = Union(Range("D9:E39"), Range("G44:I49"))
'manager sheet
Case 2: Set Rng = Range("B44:E49")
'director sheet
End Select
oSh.Range(Rng.Address).ClearContents
Set Rrg = Nothing
End If
Next i
Next
End Sub

isabelle
thanks for that however i've been told today that the list of sheets ma
change AND be added to.
I was trying to have a sheet with a list of the sheets in to file, tha
i could flag as to clear. My problem is the sheet names can change a
new staff come and go so the sheet names are not fixed.
Due to confidentially i can't upload a file. However i'm looking a
taking those bits out so i can post an example.
hopefully it will be done late tomorrow

 

[y0rk1e72]

isabelle
thanks for that however i've been told today that the list of sheets ma
change AND be added to.
I was trying to have a sheet with a list of the sheets in to file, tha
i could flag as to clear. My problem is the sheet names can change a
new staff come and go so the sheet names are not fixed.
Due to confidentially i can't upload a file. However i'm looking a
taking those bits out so i can post an example.
hopefully it will be done late tomorrow.

kinda fixed it by using the following


PHP code
-------------------

Private Sub Worksheet_Activate()
Dim c As Range, msg As String, style As String, title As String
msg = "Do you want to clear your entries?" & vbCrLf & "click No to cancel"
style = vbYesNo + vbQuestion + vbDefaultButton2
title = "Action required"
response = MsgBox(msg, style, title)
If response = vbNo Then Exit Sub
Me.Protect "test", , , , True 'change test to your password
On Error Resume Next
Range("D9:E39").Value = ""
Range("G9:i39").Value = ""
Range("B44:B49").Value = ""
Range("D44:E49").Value = ""
Range("g44:I49").Value = ""
MsgBox ("Your entries have been cleared.")
End Sub