Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make a dynamic print range in excel?
How do I make excel automatically adjust the print area to print only the
rows with information in them? Thanks for the help. Herd |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make a dynamic print range in excel?
Depends on what you mean...
If the rows at the bottom are really empty -- no formulas, no nothing, then excel should be able to figure out what should be printed. But sometimes you may have to reset the lastused cell so that it matches with what you think it should be. Visit Debra Dalgleish's site for some techniques for resetting that lastusedcell. http://www.contextures.com/xlfaqApp.html#Unused On the other hand, if you have formulas in those rows that evaluate to "", resetting the last used cell won't help. If I can pick out a column indicates if that row is used or not, then I like this technique: (I used column A in my sample, but you can use any column you want.) Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Herd wrote: How do I make excel automatically adjust the print area to print only the rows with information in them? Thanks for the help. Herd -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make a dynamic print range in excel?
When I follow your instructions, it works great the first time, then when I
add a couple rows of data... the last print range is still held If I check the named range, the function =OFFSET(Sheet1!$A$1,0,0,lastRow,3) has been replaced with the actual range "Dave Peterson" wrote: Depends on what you mean... If the rows at the bottom are really empty -- no formulas, no nothing, then excel should be able to figure out what should be printed. But sometimes you may have to reset the lastused cell so that it matches with what you think it should be. Visit Debra Dalgleish's site for some techniques for resetting that lastusedcell. http://www.contextures.com/xlfaqApp.html#Unused On the other hand, if you have formulas in those rows that evaluate to "", resetting the last used cell won't help. If I can pick out a column indicates if that row is used or not, then I like this technique: (I used column A in my sample, but you can use any column you want.) Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Herd wrote: How do I make excel automatically adjust the print area to print only the rows with information in them? Thanks for the help. Herd -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make a dynamic print range in excel?
If you go into File|Page Setup, you'll have to redo the Sheet1!Print_Area name.
tmirelle wrote: When I follow your instructions, it works great the first time, then when I add a couple rows of data... the last print range is still held If I check the named range, the function =OFFSET(Sheet1!$A$1,0,0,lastRow,3) has been replaced with the actual range "Dave Peterson" wrote: Depends on what you mean... If the rows at the bottom are really empty -- no formulas, no nothing, then excel should be able to figure out what should be printed. But sometimes you may have to reset the lastused cell so that it matches with what you think it should be. Visit Debra Dalgleish's site for some techniques for resetting that lastusedcell. http://www.contextures.com/xlfaqApp.html#Unused On the other hand, if you have formulas in those rows that evaluate to "", resetting the last used cell won't help. If I can pick out a column indicates if that row is used or not, then I like this technique: (I used column A in my sample, but you can use any column you want.) Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Herd wrote: How do I make excel automatically adjust the print area to print only the rows with information in them? Thanks for the help. Herd -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I make a dynamic print range in excel?
Hi,
To reset the print area, to the dynamic range,you can use this macro, however make sure that the defined name Print_Area in every worksheet has the formula that was previously inplemented in this discussion Macro: Sub Print_custom() With ActiveSheet ..PageSetup.PrintArea = ActiveSheet.Range("A1:A10") ..PageSetup.PrintArea = ActiveSheet.Range("Print_Area").Address End With End Sub Thanks, RaveendiranRR "Dave Peterson" wrote: If you go into File|Page Setup, you'll have to redo the Sheet1!Print_Area name. tmirelle wrote: When I follow your instructions, it works great the first time, then when I add a couple rows of data... the last print range is still held If I check the named range, the function =OFFSET(Sheet1!$A$1,0,0,lastRow,3) has been replaced with the actual range "Dave Peterson" wrote: Depends on what you mean... If the rows at the bottom are really empty -- no formulas, no nothing, then excel should be able to figure out what should be printed. But sometimes you may have to reset the lastused cell so that it matches with what you think it should be. Visit Debra Dalgleish's site for some techniques for resetting that lastusedcell. http://www.contextures.com/xlfaqApp.html#Unused On the other hand, if you have formulas in those rows that evaluate to "", resetting the last used cell won't help. If I can pick out a column indicates if that row is used or not, then I like this technique: (I used column A in my sample, but you can use any column you want.) Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Herd wrote: How do I make excel automatically adjust the print area to print only the rows with information in them? Thanks for the help. Herd -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NUMBER OF ROWS IN A DYNAMIC RANGE | Excel Worksheet Functions | |||
Using a range variable inside a excel function | Excel Discussion (Misc queries) | |||
excel 2003 changing saved print settings | Excel Discussion (Misc queries) | |||
How do I put a watermark under cells in excel and make it print? | Excel Worksheet Functions | |||
How do I make range bar graphs in Excel 2002? | Charts and Charting in Excel |