Code to set print range

[pickytweety]

District 1 Trends
Meat YTD 3 Mo
Avg Avg Nov-07 Oct-07
Store # 30
Smile 70 70 73 73
Attentive 74 69 67 68
Total 72 70 70 70
Store # 83
Smile 80 81 92 71
Attentive 75 66 65 60
Total 78 74 79 66
Store # 0
Smile #DIV/0! #DIV/0! n/a n/a
Attentive #DIV/0! #DIV/0! n/a n/a
Total #DIV/0! #DIV/0! n/a n/a
Store # 0
Smile #DIV/0! #DIV/0! n/a n/a
Attentive #DIV/0! #DIV/0! n/a n/a
Total #DIV/0! #DIV/0! n/a n/a

The "Store # 0" is just a format, the cell contains a zero.
Can somebody tell me how to write code to set the print range just above the
first cell in column A with "store # 0" in it? Also I would like to delete
all rows from the first "store # 0" down.

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

Dim res As Variant

With Worksheets("sheet1")
res = Application.Match(0, .Range("a:a"), 0)

If IsError(res) Then
MsgBox "Store 0 wasn't found"
Else
If res > 1 Then
.Rows("1:" & res - 1).Name = "'" & .Name & "'!Print_Area"
Else
MsgBox "print area not changed!"
End If
.Rows(res & ":" & .Rows.Count).Delete
End If
End With
End Sub