In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.

I'd like to build a loop to vary the row height but can't find how to
access the page number I see in gray in the background of the page break
view. (any other page number would also be fine.)

the gist of the desired logic is: ( in 'pseudo' code)

rows("1:" & lastrow).rowheight = big_number ' to be more than 2 pages
do until pages = 2
big_number = big_number - .5
loop

If there are other techniques, as always, any help is much appreciated.
--
Neal Z

On Sat, 30 Apr 2005 21:09:02 -0700, Neal Zimm
wrote:

In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.

can't find how to access the page number I see in gray in the background of the page break

See Excel VBA help for HPageBreaks Collection Object. A simple use is
debug.print activesheet.HPageBreaks.Count
That's useful for seeing if you have greater than 2 pages.

You might want to drill the help to property Items, e.g.
Debug.Print Worksheets(i).HPageBreaks.Item(i).Location
but be aware that it produces a runtime error if i exceeds the .Count.

This does not per se provide the "page number" you asked for for a given
cell; is this approach adequate?

Neal,

This ought to get you close...
'--------------------------------------
Sub BigButNotToBig()
'Jim Cone - San Francisco, USA - May 01, 2005
'Increases row height of data area to the maximum
'height that will fit on two printed pages.
'When complete, all rows in the data area will be the same height.


Dim rngAll As Excel.Range
Dim lngSize As Long
Dim dblPageCount As Double
Const lngIncrement As Long = 1

dblPageCount = ExecuteExcel4Macro("get.document(50)")
If dblPageCount 2 Then
MsgBox "Sheet has more than two printable pages. "
Exit Sub
End If

Set rngAll = ActiveSheet.UsedRange
Application.ScreenUpdating = False
If IsNull(rngAll.RowHeight) Then
rngAll.RowHeight = ActiveSheet.StandardHeight
dblPageCount = ExecuteExcel4Macro("get.document(50)")
End If

lngSize = rngAll.RowHeight
Do
If dblPageCount < 3 Then
lngSize = lngSize + lngIncrement
rngAll.RowHeight = lngSize
dblPageCount = ExecuteExcel4Macro("get.document(50)")
Else
Exit Do
End If
Loop

rngAll.RowHeight = lngSize - lngIncrement
Application.ScreenUpdating = True
MsgBox "Row height is " & (lngSize - lngIncrement)
Set rngAll = Nothing
End Sub
'----------------------------



"Neal Zimm" wrote in message
...
In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.
I'd like to build a loop to vary the row height but can't find how to
access the page number I see in gray in the background of the page break
view. (any other page number would also be fine.)
the gist of the desired logic is: ( in 'pseudo' code)

rows("1:" & lastrow).rowheight = big_number ' to be more than 2 pages
do until pages = 2
big_number = big_number - .5
loop

If there are other techniques, as always, any help is much appreciated.
Neal Z

In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.

can't find how to access the page number I see in gray in the background of the page break

See Excel VBA help for HPageBreaks Collection Object. A simple use is
debug.print activesheet.HPageBreaks.Count

I might have also mentioned VPageBreaks, but you didn't indicate concern
there. (VPageBreaks.count+1)*(HPageBreaks.count+1) is your print page
total. (So it's (HPageBreaks.count+1) if the only concern is height.)

Since low row heights may clip your display, and large ones cause great
white space, you might think of incrementally bumping the font instead
of rowsize - maybe autofit the row heights and then walk font sizes. Of
course, at some point a font size may cause the print width to trip a
page break, so you'd need to stay on top of VPageBreaks.count for that.

Wild Bill <--needs a spotlight or reading glasses to read some phone
books

Thanks. I'll look into your solution and get back to you. As I'm relatively
new to VB I browse a lot of the problems here and see " debug.print
worksheets" a lot.
What is this and how to I find out more about this in the Excel help?
thanks,
Neal


"Wild Bill" wrote:

On Sat, 30 Apr 2005 21:09:02 -0700, Neal Zimm
wrote:

In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.

can't find how to access the page number I see in gray in the background of the page break

See Excel VBA help for HPageBreaks Collection Object. A simple use is
debug.print activesheet.HPageBreaks.Count
That's useful for seeing if you have greater than 2 pages.

You might want to drill the help to property Items, e.g.
Debug.Print Worksheets(i).HPageBreaks.Item(i).Location
but be aware that it produces a runtime error if i exceeds the .Count.

This does not per se provide the "page number" you asked for for a given
cell; is this approach adequate?

Jim -
Thanks. I'm kinda new to this and was able to follow your code but do not
know what the following is: I'll give it a try and get back to you.

dblPageCount = ExecuteExcel4Macro("get.document(50)")

can you please give me a word or two the execute excel4 macro and just
what is 'get.document(50)' ?

Thanks again,
Neal


"Jim Cone" wrote:

Neal,

This ought to get you close...
'--------------------------------------
Sub BigButNotToBig()
'Jim Cone - San Francisco, USA - May 01, 2005
'Increases row height of data area to the maximum
'height that will fit on two printed pages.
'When complete, all rows in the data area will be the same height.


Dim rngAll As Excel.Range
Dim lngSize As Long
Dim dblPageCount As Double
Const lngIncrement As Long = 1

dblPageCount = ExecuteExcel4Macro("get.document(50)")
If dblPageCount 2 Then
MsgBox "Sheet has more than two printable pages. "
Exit Sub
End If

Set rngAll = ActiveSheet.UsedRange
Application.ScreenUpdating = False
If IsNull(rngAll.RowHeight) Then
rngAll.RowHeight = ActiveSheet.StandardHeight
dblPageCount = ExecuteExcel4Macro("get.document(50)")
End If

lngSize = rngAll.RowHeight
Do
If dblPageCount < 3 Then
lngSize = lngSize + lngIncrement
rngAll.RowHeight = lngSize
dblPageCount = ExecuteExcel4Macro("get.document(50)")
Else
Exit Do
End If
Loop

rngAll.RowHeight = lngSize - lngIncrement
Application.ScreenUpdating = True
MsgBox "Row height is " & (lngSize - lngIncrement)
Set rngAll = Nothing
End Sub
'----------------------------



"Neal Zimm" wrote in message
...
In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.
I'd like to build a loop to vary the row height but can't find how to
access the page number I see in gray in the background of the page break
view. (any other page number would also be fine.)
the gist of the desired logic is: ( in 'pseudo' code)

rows("1:" & lastrow).rowheight = big_number ' to be more than 2 pages
do until pages = 2
big_number = big_number - .5
loop

If there are other techniques, as always, any help is much appreciated.
Neal Z

Neal,

"ExecuteExcel4Macro" is a VBA method that runs a Microsoft
Excel 4.0 macro function and then returns the result of the function.

"Get.Document (arg1,arg2)" is a XL4 function with two arguments.

The second argument is optional (assumes active sheet).
The first argument specifies the type of information returned.
The range of values for the first argument runs from 1 to 68...
For example 1 returns the worksheet name, 2 returns the path and
50 returns the number of printed pages.

XL4 macro code continues to work thru Excel version 2002 and,
I believe, Excel 2003. As used, it is not very fast but has been
dependable.

Regards,
Jim Cone
San Francisco, USA


"Neal Zimm" wrote in message
...
Jim -
Thanks. I'm kinda new to this and was able to follow your code but do not
know what the following is: I'll give it a try and get back to you.
dblPageCount = ExecuteExcel4Macro("get.document(50)")
can you please give me a word or two the execute excel4 macro and just
what is 'get.document(50)' ?
Thanks again,
Neal


"Jim Cone" wrote:
Neal,
This ought to get you close...
'--------------------------------------
Sub BigButNotToBig()
'Jim Cone - San Francisco, USA - May 01, 2005
'Increases row height of data area to the maximum
'height that will fit on two printed pages.
'When complete, all rows in the data area will be the same height.


Dim rngAll As Excel.Range
Dim lngSize As Long
Dim dblPageCount As Double
Const lngIncrement As Long = 1

dblPageCount = ExecuteExcel4Macro("get.document(50)")
If dblPageCount 2 Then
MsgBox "Sheet has more than two printable pages. "
Exit Sub
End If

Set rngAll = ActiveSheet.UsedRange
Application.ScreenUpdating = False
If IsNull(rngAll.RowHeight) Then
rngAll.RowHeight = ActiveSheet.StandardHeight
dblPageCount = ExecuteExcel4Macro("get.document(50)")
End If

lngSize = rngAll.RowHeight
Do
If dblPageCount < 3 Then
lngSize = lngSize + lngIncrement
rngAll.RowHeight = lngSize
dblPageCount = ExecuteExcel4Macro("get.document(50)")
Else
Exit Do
End If
Loop

rngAll.RowHeight = lngSize - lngIncrement
Application.ScreenUpdating = True
MsgBox "Row height is " & (lngSize - lngIncrement)
Set rngAll = Nothing
End Sub
'----------------------------



"Neal Zimm" wrote in message
...
In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.
I'd like to build a loop to vary the row height but can't find how to
access the page number I see in gray in the background of the page break
view. (any other page number would also be fine.)
the gist of the desired logic is: ( in 'pseudo' code)

rows("1:" & lastrow).rowheight = big_number ' to be more than 2 pages
do until pages = 2
big_number = big_number - .5
loop

If there are other techniques, as always, any help is much appreciated.
Neal Z

Thanks so much. "Wild Bill" also responded to my question and mentioned the
Hpagebreaks.count property. After trying both your methods, I think the
..count method is a bit simpler and I'm going to use it.
Thanks again for answering and for the 411 on the exec4 macro question.
Neal


"Jim Cone" wrote:

Neal,

"ExecuteExcel4Macro" is a VBA method that runs a Microsoft
Excel 4.0 macro function and then returns the result of the function.

"Get.Document (arg1,arg2)" is a XL4 function with two arguments.

The second argument is optional (assumes active sheet).
The first argument specifies the type of information returned.
The range of values for the first argument runs from 1 to 68...
For example 1 returns the worksheet name, 2 returns the path and
50 returns the number of printed pages.

XL4 macro code continues to work thru Excel version 2002 and,
I believe, Excel 2003. As used, it is not very fast but has been
dependable.

Regards,
Jim Cone
San Francisco, USA


"Neal Zimm" wrote in message
...
Jim -
Thanks. I'm kinda new to this and was able to follow your code but do not
know what the following is: I'll give it a try and get back to you.
dblPageCount = ExecuteExcel4Macro("get.document(50)")
can you please give me a word or two the execute excel4 macro and just
what is 'get.document(50)' ?
Thanks again,
Neal


"Jim Cone" wrote:
Neal,
This ought to get you close...
'--------------------------------------
Sub BigButNotToBig()
'Jim Cone - San Francisco, USA - May 01, 2005
'Increases row height of data area to the maximum
'height that will fit on two printed pages.
'When complete, all rows in the data area will be the same height.


Dim rngAll As Excel.Range
Dim lngSize As Long
Dim dblPageCount As Double
Const lngIncrement As Long = 1

dblPageCount = ExecuteExcel4Macro("get.document(50)")
If dblPageCount 2 Then
MsgBox "Sheet has more than two printable pages. "
Exit Sub
End If

Set rngAll = ActiveSheet.UsedRange
Application.ScreenUpdating = False
If IsNull(rngAll.RowHeight) Then
rngAll.RowHeight = ActiveSheet.StandardHeight
dblPageCount = ExecuteExcel4Macro("get.document(50)")
End If

lngSize = rngAll.RowHeight
Do
If dblPageCount < 3 Then
lngSize = lngSize + lngIncrement
rngAll.RowHeight = lngSize
dblPageCount = ExecuteExcel4Macro("get.document(50)")
Else
Exit Do
End If
Loop

rngAll.RowHeight = lngSize - lngIncrement
Application.ScreenUpdating = True
MsgBox "Row height is " & (lngSize - lngIncrement)
Set rngAll = Nothing
End Sub
'----------------------------



"Neal Zimm" wrote in message
...
In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.
I'd like to build a loop to vary the row height but can't find how to
access the page number I see in gray in the background of the page break
view. (any other page number would also be fine.)
the gist of the desired logic is: ( in 'pseudo' code)

rows("1:" & lastrow).rowheight = big_number ' to be more than 2 pages
do until pages = 2
big_number = big_number - .5
loop

If there are other techniques, as always, any help is much appreciated.
Neal Z

Hpagebreaks.count absolutely did the trick. The loop works just as I need it.
I did not mention Vpagebreaks since it's not an issue in the application.

Also not mentioned, since I thought it would muddy the waters, is that the
number of rows involved will not vary all that much, maybe + or - 5 rows on
an average of 50, so varying the font size probably will not come into play.

Thanks again for your complete answer. What does gets me nuts, however, is
that I looked at the properties in VB help and could not find what I wanted.
After the fact I entered "page breaks" in the help search area and got
listings for hpagebreaks. I guess it's a matter of a little luck when you
look for something that you find it, especially with not a lot of experience
like me, when you search for the wrong thing. E.G. me looking for page
counts and not page breaks.

Again thanks,
Neal Z.


"Wild Bill" wrote:

In my application it's important for one worksheet to be ONLY two pages long
and yet have the row height be the maximum it can be.

can't find how to access the page number I see in gray in the background of the page break

See Excel VBA help for HPageBreaks Collection Object. A simple use is
debug.print activesheet.HPageBreaks.Count

I might have also mentioned VPageBreaks, but you didn't indicate concern
there. (VPageBreaks.count+1)*(HPageBreaks.count+1) is your print page
total. (So it's (HPageBreaks.count+1) if the only concern is height.)

Since low row heights may clip your display, and large ones cause great
white space, you might think of incrementally bumping the font instead
of rowsize - maybe autofit the row heights and then walk font sizes. Of
course, at some point a font size may cause the print width to trip a
page break, so you'd need to stay on top of VPageBreaks.count for that.

Wild Bill <--needs a spotlight or reading glasses to read some phone
books

**** WARNING - .hpagebreaks.count ****

The theatrics are because I am amending what I patly asserted earlier.

(VPageBreaks.count+1)*(HPageBreaks.count+1) is your print page total
..hpagebreaks.count+1 is NOT the total vertical page count if there is a
HARD page break at bottom of the print range. In THAT case,
..hpagebreaks.count is the total vertical page count.
..vpagebreaks.count behaves analogously with a hard columnar page break.

Verified on XL97, XL03. Other than self-policing that there are no hard
page breaks at the edge of the print range (which might be just cool for
you, Neal), the only prohibitive solution that I could see would be to
test .HPageBreaks.Item(.hpagebreaks.count).Location against the bottom
row of the print area. And the same would apply for testing vpagebreaks.

Sorry - I just discovered this, and I feel like a pinhead for neglecting
this if anyone reading the thread uses .Count without reading this far.

A hard page break results from menu Insert/Break/Page Break. To my
knowledge, hard breaks are neither created nor destroyed by manipulating
the Page Break Preview interface, where you can drag break locations.

There are 20 or more REAL gurus here that are intimately familiar with
Excel internals, in case any of them care to weigh in.

Furthermore, .hpagebreaks.count will include "unused" hard page breaks
when print to fit is used (set by code or File /page setup/scaling. If
print to fit is used, and if any hard breaks exist, the relationship of
..hpagebreaks.count to actual page count would not be reliable. (So here
the hard break wouldn't even have to be at the edge of the print range.)

If any of this can matter to you, you might rethink Get.Document or
other suggestions from Jim or Tom or another that knows what the heck
they're doing :-O