Another Print Area question

[Guest]

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

 

[Guest]

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

 

[Gord Dibben]

You don't need code.

You could use a Dynamic named range via Insert>Name>Define

=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

 

[BDXZH]

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)+COUNTIF(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

You don't need code.

You could use a Dynamic named range via Insert>Name>Define

=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

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