Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Ranges
Hi folks,
Hope someone can help with this printing query. - I have a worksheet ("sheet1") which has 100 rows that may contain data. - Rows 1 to 40 are set to print at the top of every page. - The page set up allows for rows 1:40 + another 15. So if my print range was rows 1:70 then i'd get 2 pages, rows 1:100 = 4 pages etc. Now i could easily set the macro to print range 1:100 but if i only had data in rows 1:70 i'd have 2 pages printing that i don't need. Not to mention the trees i'd be wasting. I've been trying to find away that i can determine where the data ends and selecting a print range accordingly. To do this i thought about working out how many blank cells there is in range A41:A100 (say it returns a value of 30) storing that value in a cell (or could i calculate it in the coding?) and then adding / deducting that value from the fixed row numbers of 40 / 100. I tried this but it doesn't seem to work ...... Set Blanks = Worksheets("sheet1").Cell("A40").Value Set Row = "A1:I" & 100 - Blanks Sheets("sheet1").Select Range(Row).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub Can anyone help put me on the right track? I'll bet it's really simple and i'm trying to make it complicated !!! TIA Craig. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Ranges
set rng = cells(rows.count,1).End(xlup)
Activesheet.PageSetup.Printarea = _ range("A41",rng).Resize(,9).Address(external:=True ) -- Regards, Tom Ogilvy "Craig" wrote in message ... Hi folks, Hope someone can help with this printing query. - I have a worksheet ("sheet1") which has 100 rows that may contain data. - Rows 1 to 40 are set to print at the top of every page. - The page set up allows for rows 1:40 + another 15. So if my print range was rows 1:70 then i'd get 2 pages, rows 1:100 = 4 pages etc. Now i could easily set the macro to print range 1:100 but if i only had data in rows 1:70 i'd have 2 pages printing that i don't need. Not to mention the trees i'd be wasting. I've been trying to find away that i can determine where the data ends and selecting a print range accordingly. To do this i thought about working out how many blank cells there is in range A41:A100 (say it returns a value of 30) storing that value in a cell (or could i calculate it in the coding?) and then adding / deducting that value from the fixed row numbers of 40 / 100. I tried this but it doesn't seem to work ...... Set Blanks = Worksheets("sheet1").Cell("A40").Value Set Row = "A1:I" & 100 - Blanks Sheets("sheet1").Select Range(Row).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub Can anyone help put me on the right track? I'll bet it's really simple and i'm trying to make it complicated !!! TIA Craig. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Ranges
Hi Tom,
Thanks for the reply. I should have said that cells A41:A100 on ("sheet1") have formula which pulls data from another sheet if the cell is <"" This means it thinks the last used cell is A100 ... is there away to incorporate an IsEmpty ? Thanks again, Craig -----Original Message----- set rng = cells(rows.count,1).End(xlup) Activesheet.PageSetup.Printarea = _ range("A41",rng).Resize(,9).Address(external:=True ) -- Regards, Tom Ogilvy "Craig" wrote in message ... Hi folks, Hope someone can help with this printing query. - I have a worksheet ("sheet1") which has 100 rows that may contain data. - Rows 1 to 40 are set to print at the top of every page. - The page set up allows for rows 1:40 + another 15. So if my print range was rows 1:70 then i'd get 2 pages, rows 1:100 = 4 pages etc. Now i could easily set the macro to print range 1:100 but if i only had data in rows 1:70 i'd have 2 pages printing that i don't need. Not to mention the trees i'd be wasting. I've been trying to find away that i can determine where the data ends and selecting a print range accordingly. To do this i thought about working out how many blank cells there is in range A41:A100 (say it returns a value of 30) storing that value in a cell (or could i calculate it in the coding?) and then adding / deducting that value from the fixed row numbers of 40 / 100. I tried this but it doesn't seem to work ...... Set Blanks = Worksheets("sheet1").Cell("A40").Value Set Row = "A1:I" & 100 - Blanks Sheets("sheet1").Select Range(Row).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub Can anyone help put me on the right track? I'll bet it's really simple and i'm trying to make it complicated !!! TIA Craig. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Ranges
set rng = cells(rows.count,1).End(xlup)
do while trim(rng.Value) ="" set rng = rng.offset(-1,0) Loop Activesheet.PageSetup.Printarea = _ range("A41",rng).Resize(,9).Address(external:=True ) -- Regards, Tom Ogilvy wrote in message ... Hi Tom, Thanks for the reply. I should have said that cells A41:A100 on ("sheet1") have formula which pulls data from another sheet if the cell is <"" This means it thinks the last used cell is A100 ... is there away to incorporate an IsEmpty ? Thanks again, Craig -----Original Message----- set rng = cells(rows.count,1).End(xlup) Activesheet.PageSetup.Printarea = _ range("A41",rng).Resize(,9).Address(external:=True ) -- Regards, Tom Ogilvy "Craig" wrote in message ... Hi folks, Hope someone can help with this printing query. - I have a worksheet ("sheet1") which has 100 rows that may contain data. - Rows 1 to 40 are set to print at the top of every page. - The page set up allows for rows 1:40 + another 15. So if my print range was rows 1:70 then i'd get 2 pages, rows 1:100 = 4 pages etc. Now i could easily set the macro to print range 1:100 but if i only had data in rows 1:70 i'd have 2 pages printing that i don't need. Not to mention the trees i'd be wasting. I've been trying to find away that i can determine where the data ends and selecting a print range accordingly. To do this i thought about working out how many blank cells there is in range A41:A100 (say it returns a value of 30) storing that value in a cell (or could i calculate it in the coding?) and then adding / deducting that value from the fixed row numbers of 40 / 100. I tried this but it doesn't seem to work ...... Set Blanks = Worksheets("sheet1").Cell("A40").Value Set Row = "A1:I" & 100 - Blanks Sheets("sheet1").Select Range(Row).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub Can anyone help put me on the right track? I'll bet it's really simple and i'm trying to make it complicated !!! TIA Craig. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Print Ranges
Thanks Tom
Works a treat, much appreciated. Cheers, Craig. -----Original Message----- set rng = cells(rows.count,1).End(xlup) do while trim(rng.Value) ="" set rng = rng.offset(-1,0) Loop Activesheet.PageSetup.Printarea = _ range("A41",rng).Resize(,9).Address(external:=True ) -- Regards, Tom Ogilvy wrote in message ... Hi Tom, Thanks for the reply. I should have said that cells A41:A100 on ("sheet1") have formula which pulls data from another sheet if the cell is <"" This means it thinks the last used cell is A100 ... is there away to incorporate an IsEmpty ? Thanks again, Craig -----Original Message----- set rng = cells(rows.count,1).End(xlup) Activesheet.PageSetup.Printarea = _ range("A41",rng).Resize(,9).Address(external:=True ) -- Regards, Tom Ogilvy "Craig" wrote in message ... Hi folks, Hope someone can help with this printing query. - I have a worksheet ("sheet1") which has 100 rows that may contain data. - Rows 1 to 40 are set to print at the top of every page. - The page set up allows for rows 1:40 + another 15. So if my print range was rows 1:70 then i'd get 2 pages, rows 1:100 = 4 pages etc. Now i could easily set the macro to print range 1:100 but if i only had data in rows 1:70 i'd have 2 pages printing that i don't need. Not to mention the trees i'd be wasting. I've been trying to find away that i can determine where the data ends and selecting a print range accordingly. To do this i thought about working out how many blank cells there is in range A41:A100 (say it returns a value of 30) storing that value in a cell (or could i calculate it in the coding?) and then adding / deducting that value from the fixed row numbers of 40 / 100. I tried this but it doesn't seem to work ...... Set Blanks = Worksheets("sheet1").Cell("A40").Value Set Row = "A1:I" & 100 - Blanks Sheets("sheet1").Select Range(Row).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub Can anyone help put me on the right track? I'll bet it's really simple and i'm trying to make it complicated !!! TIA Craig. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges | Excel Worksheet Functions | |||
Print Area ranges print on separate pages? | Excel Discussion (Misc queries) | |||
Dynamic Ranges with ADO | Excel Discussion (Misc queries) | |||
Dynamic Ranges | Excel Discussion (Misc queries) | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions |