How many rows fit a single page?

[Ed from AZ]

(Word and Excel 2003) I have an Excel table that my code scans, picks
so many rows, and then copies that into a Word document as a table.
This worked okay when all my rows were a single fixed height. I've
just received instructions to add some info items to this table, which
will increase some row sizes due to word wrap.

Is there a good way to determine how many rows will fit inside the
margins of my Word page? Perhaps add the height of each row until I
get to a certain number? Or can I detect and use the page break
somehow for this?

Ed

 

[Jim Thomlinson]

You are going to have certain difficulties with this as it depends on the
margins you set in Word. When those change your code will need to change.
Here is some simple code to get you started. It does not look at the margins
in Word...

Sub test()
Dim dbl As Double
Dim rng As Range
Dim rngToSearch As Range

Set rngToSearch = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each rng In rngToSearch
dbl = dbl + rng.Height
If dbl > 400 Then 'You will need to change this to suit
MsgBox rng.Address
dbl = 0
End If
Next rng
End Sub

 

[Howard Kaikow]

You are going to have certain difficulties with this as it depends on the
margins you set in Word. When those change your code will need to change.
Here is some simple code to get you started. It does not look at the margins
in Word...

Sub test()
Dim dbl As Double
Dim rng As Range
Dim rngToSearch As Range

Set rngToSearch = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each rng In rngToSearch
dbl = dbl + rng.Height
If dbl > 400 Then 'You will need to change this to suit
MsgBox rng.Address
dbl = 0
End If
Next rng
End Sub

In Word, the style of each paragraph, as well as the number of lines
before/after, etc. must be considered.

Word has the ability to break rows at appropriate points and repeat headers
on each page.
Do you need more than that?

 

[Ed from AZ]

Thanks, Jim. The margins for this will not change.

What is the "400"? Points? Or just an arbitrary number used for
measurement according to whatever Excel is using?

Ed

 

[Ed from AZ]

Hi, Howard. part of the issue here is that for this report, I need to
have a header for the table. If the table breaks over a page, the
header has to say "Table 1 (continued)", except on the last page where
it becomes "Table 1 (concluded)." If I know how many pages I'm
spreading onto, I can set the headers and then copy over the rows.
Yes, I could just format the table to repeat the top rows as header
and do not break rows across pages, but then I lose that ability to
set the header on top. Unless you know of a way . . .

Ed