Print Area propery

[Marc]

I would like to use the property Print Area. This property
requires two give two cells for the array: top-left corner
and the bottom-right one. The top-left corner is constant
at $A$1. The bottom right has a constant column "K" but
its row varies.

Worksheets("Sheet1").PageSetup.PrintArea = "$A$1:$K$?"

How could I specify the value for the row in the PrintArea
property taking into account that this value is calculated
as follows?

Column F is a validation list arranged in alphabetical
order. The six options for the validation list are: 1.OPER
2.MISS 3.EXEM 4.DISC 5.REMO 6.COMI

Considering that entries are in alphabetical order, I
would like to print only up to the last 4.DISC included.
This means that the row of the bottom-left corner of the
array is the previous entry to the first one that
has "5.REMO" in column F

Can anyone provide me with the code?

Thank you very much,

Marc

 

[Dave Peterson]

You could use .find to search up that column:

Option Explicit

Sub testme()

Dim FoundCell As Range
Dim LookFor As String

LookFor = "4.DISC"
With Worksheets("Sheet1")
With .Range("F:F")
Set FoundCell = .Cells.Find(what:=LookFor, _
after:=.Cells(1), _
searchdirection:=xlPrevious, _
LookIn:=xlValues, lookat:=xlWhole, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
MsgBox "can't set range!"
Else
.PageSetup.PrintArea = "$A$1:K" & FoundCell.Row
End If

End With

End Sub

Watch out for the parms I used--xlwhole and matchcase.