Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Another Print Area question
I have a spreadsheet that has a varying number of rows to print depending on
the amount of data. The columns are standard A:F but the rows could be 3 or 33. I have had a couple of goes but can't get it to play. Columns F have data but don't print. The cells after the last non blank have formulas that return "". So, what I need is a code that will set the print area as A1:the last non blank cell in Column F but not detect the formulas returning "" below the last non blank. Currently I have the print settings set to return [page]of[pages] as a footer and A1:F1 at the top of each page. Am I correct in assuming that specifying the print area won't alter those settings? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Another Print Area question
hi
if i understand you correctly, this should work for you. if not, play with the offsets. Dim r As Range Set r = Range(Range("A1"), Range("A1") _ .End(xlToRight).End(xlDown).Offset(-1, 0)) ActiveSheet.PageSetup.PrintArea = r.Address this should not affect your headers and footers. Regards FSt1 "curiosity_killed_the_cat" wrote: I have a spreadsheet that has a varying number of rows to print depending on the amount of data. The columns are standard A:F but the rows could be 3 or 33. I have had a couple of goes but can't get it to play. Columns F have data but don't print. The cells after the last non blank have formulas that return "". So, what I need is a code that will set the print area as A1:the last non blank cell in Column F but not detect the formulas returning "" below the last non blank. Currently I have the print settings set to return [page]of[pages] as a footer and A1:F1 at the top of each page. Am I correct in assuming that specifying the print area won't alter those settings? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Another Print Area question
You don't need code.
You could use a Dynamic named range via InsertNameDefine =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6) This will give you the the printarea for columns A:F and ignores the "" returned by your formulas. Name it Print_Area Gord Dibben MS Excel MVP On Sat, 10 Nov 2007 22:50:00 -0800, curiosity_killed_the_cat wrote: I have a spreadsheet that has a varying number of rows to print depending on the amount of data. The columns are standard A:F but the rows could be 3 or 33. I have had a couple of goes but can't get it to play. Columns F have data but don't print. The cells after the last non blank have formulas that return "". So, what I need is a code that will set the print area as A1:the last non blank cell in Column F but not detect the formulas returning "" below the last non blank. Currently I have the print settings set to return [page]of[pages] as a footer and A1:F1 at the top of each page. Am I correct in assuming that specifying the print area won't alter those settings? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Another Print Area question
Is it possible to use this funciton to count all used rows including blank
rows? I was able to modify the function in Excel 2000 to do this by using =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)+COUNT IF(Sheet1!$A:$A,""),6) In Excel 2000 this works fine as the COUNTIF function returns empty cells in the used range. However I also have some users working with Excel 2007 and the COUNTIF function returns all empty cells in the range up to the maximum rows in the sheet. Any suggestions? Thanks "Gord Dibben" wrote: You don't need code. You could use a Dynamic named range via InsertNameDefine =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6) This will give you the the printarea for columns A:F and ignores the "" returned by your formulas. Name it Print_Area Gord Dibben MS Excel MVP On Sat, 10 Nov 2007 22:50:00 -0800, curiosity_killed_the_cat wrote: I have a spreadsheet that has a varying number of rows to print depending on the amount of data. The columns are standard A:F but the rows could be 3 or 33. I have had a couple of goes but can't get it to play. Columns F have data but don't print. The cells after the last non blank have formulas that return "". So, what I need is a code that will set the print area as A1:the last non blank cell in Column F but not detect the formulas returning "" below the last non blank. Currently I have the print settings set to return [page]of[pages] as a footer and A1:F1 at the top of each page. Am I correct in assuming that specifying the print area won't alter those settings? Thanks |
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 | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
How do you turn off a print area for a page? (no print area) | Excel Discussion (Misc queries) |