Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Area propery
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 a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Area propery
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. Marc wrote: 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 a 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set area in excel not giving me option to set print area? | Excel Discussion (Misc queries) | |||
File, print area, clear area, is not working | New Users to Excel | |||
print area across the freeze panes area | Excel Worksheet Functions | |||
How do you turn off a print area for a page? (no print area) | Excel Discussion (Misc queries) | |||
Listbox Propery code | Excel Programming |