Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjustable print range
Morning,
I have a file with 11 sheets and I need a macro to print sheets 2 and 4-11. The print ranges are changeable every time on sheets 4 to 11. Sheet 2 I need to print A20:N34 Sheets 4 to 11 I need to print A1:OX In order to find the X I have a list of numbers in col R. Starting in row 1 to row X I have numbers greater than 0. Row X will have a number 0 and row X+1 = 0, always. Example 1 €“ in col R I have numbers that do 8 8 8 8 1 2 3 4 5 6 7 8 9 0 0 0 0 0 0 - print range needed is A1:O13 Example 2 €“ in col R I have numbers that do 3 3 3 3 1 2 3 4 0 0 0 0 0 0 - print range needed A1:O8 In order to find the last row it can either be done by finding the cell at which the R col changes to zero or the row at which max(R1:R100) occurs. Is there a way to set a code to find the row reference I need and set an adjustable rpint range accordingly? Thanks LiAD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjustable print range
Try the below
Sub Macro() Dim intSheet As Integer, lngRow As Long Sheets(2).Range("A20:N34").PrintOut Copies:=1, Collate:=True For intSheet = 4 To 11 lngRow = Evaluate("=MAX(IF('" & Sheets(intSheet).Name & _ "'!R1:R100<0,ROW('" & Sheets(intSheet).Name & "'!R1:R100)))") If lngRow < 0 Then Sheets(intSheet).Range("A1:O" & lngRow).PrintOut Copies:=1, Collate:=True End If Next End Sub -- Jacob "LiAD" wrote: Morning, I have a file with 11 sheets and I need a macro to print sheets 2 and 4-11. The print ranges are changeable every time on sheets 4 to 11. Sheet 2 I need to print A20:N34 Sheets 4 to 11 I need to print A1:OX In order to find the X I have a list of numbers in col R. Starting in row 1 to row X I have numbers greater than 0. Row X will have a number 0 and row X+1 = 0, always. Example 1 €“ in col R I have numbers that do 8 8 8 8 1 2 3 4 5 6 7 8 9 0 0 0 0 0 0 - print range needed is A1:O13 Example 2 €“ in col R I have numbers that do 3 3 3 3 1 2 3 4 0 0 0 0 0 0 - print range needed A1:O8 In order to find the last row it can either be done by finding the cell at which the R col changes to zero or the row at which max(R1:R100) occurs. Is there a way to set a code to find the row reference I need and set an adjustable rpint range accordingly? Thanks LiAD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adjustable print range
Perfecto
Thanks a lot "Jacob Skaria" wrote: Try the below Sub Macro() Dim intSheet As Integer, lngRow As Long Sheets(2).Range("A20:N34").PrintOut Copies:=1, Collate:=True For intSheet = 4 To 11 lngRow = Evaluate("=MAX(IF('" & Sheets(intSheet).Name & _ "'!R1:R100<0,ROW('" & Sheets(intSheet).Name & "'!R1:R100)))") If lngRow < 0 Then Sheets(intSheet).Range("A1:O" & lngRow).PrintOut Copies:=1, Collate:=True End If Next End Sub -- Jacob "LiAD" wrote: Morning, I have a file with 11 sheets and I need a macro to print sheets 2 and 4-11. The print ranges are changeable every time on sheets 4 to 11. Sheet 2 I need to print A20:N34 Sheets 4 to 11 I need to print A1:OX In order to find the X I have a list of numbers in col R. Starting in row 1 to row X I have numbers greater than 0. Row X will have a number 0 and row X+1 = 0, always. Example 1 €“ in col R I have numbers that do 8 8 8 8 1 2 3 4 5 6 7 8 9 0 0 0 0 0 0 - print range needed is A1:O13 Example 2 €“ in col R I have numbers that do 3 3 3 3 1 2 3 4 0 0 0 0 0 0 - print range needed A1:O8 In order to find the last row it can either be done by finding the cell at which the R col changes to zero or the row at which max(R1:R100) occurs. Is there a way to set a code to find the row reference I need and set an adjustable rpint range accordingly? Thanks LiAD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adjustable range radar chart | Charts and Charting in Excel | |||
Adjustable Macro Code???? | Excel Programming | |||
Chart adjustable range | Charts and Charting in Excel | |||
Adjustable Font Size | Excel Programming | |||
Adjustable Rate APR | Excel Discussion (Misc queries) |