Delete range on open

[Guest]

Hello, I've sure appreciated the help that I've gotten and found on this sight.
However I have a small problem.
I am wanting to Clear a range "j29:q38" on several sheets when a workbook
opens. The sheet names vary and can increase or decrease in number. I do have
a range "ZZListing a2:a300" that contains employee names which correspond to
the sheet names that I I want to clear. But I have six other sheets, that I
don't want to clear, the names of these sheets all begin with ZZ, ie
ZZListing, ZZProjects, etc. I hope that I am explaining myself.
Here is the last code that I've tried.

Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name < "ZZ" Then
Range("j29:q38").ClearContents

End If
Next

 

[Brian Taylor]

You want the left function.

Dim WS As Worksheet
For Each WS In Worksheets
If left(WS.Name,2) = "ZZ" Then
Range("j29:q38").ClearContents
End If
Next

 

[Brian Taylor]

Sorry, I misunderstood slightly. Try this:

Dim WS As Worksheet
For Each WS In Worksheets
If not left(WS.Name,2) = "ZZ" Then
Range("j29:q38").ClearContents
End If
Next

 

[Rick Hansen]

Good Morning, here is bit of code that will propbably get you pointed in the
right direction. If you have any questions, posted me back...

enjoy, Rick (Fairbanks, AK {Land of the Midnight Sun})

===============================

Option Explicit

Sub ClearZone()
Dim ws As Worksheet
Dim trgRng As Range, ckCell As Range

'' Assume ZZ list on Sheet1
Set trgRng = Worksheets("Sheet1").Range("A2:A300")

For Each ckCell In trgRng
If Not Left(ckCell.Value, 2) = "ZZ" Then
If Not IsEmpty(ckCell.Value) Then
Set ws = Worksheets(ckCell.Value)
ws.Range("j29:q38").ClearContents
Else
Exit Sub '' found empty cell
End If
End If
Next ckCell

End Sub

=================================

 

[Guest]

Thanks Brian for you quick response, but
I don't understand why but it deleted the range in the worksheets that begin
with "ZZ". Here's what it looks like
Private Sub workbook_open()
'clear A9s
Dim WS As Worksheet
For Each WS In Worksheets
If Not Left(WS.Name, 2) = "ZZ" Then
Range("j29:q38").ClearContents
End If
Next

 

[Guest]

Hello Rick and thanks
Butl I get a "subscipt out of range" on this line
Set ws = Worksheets(ckCell.Value)
I changed the "sheet1" to "ZZZListing" where the sheet list name is located.

 

[Guest]

Sorry, I got it to work. I had the range as a2:a300 when it should have been
a3:a300.
Thanks so much for you help

 

[Rick Hansen]

It was my pleasure...

hth, Rick

Sorry, I got it to work. I had the range as a2:a300 when it should have been
a3:a300.
Thanks so much for you help