Get last row of a page

[kwibis]

After spending countless hours of searching my issue I decided to register at this forum for help.

I make quotations using excel and with the help of vba I automate some things. Now I want to place the area for signatures on the bottom of the A4-papersize page in excel. However, I cannot figure out how to determine the last row on the page.

I can figure out the last used row:

Sheets("Offerte").UsedRange.Rows.Count

But as there are cells with text wrapping I need the vba code to also return the last row of the page. I was thinking of adjusting the "UsedRange" to firstpage or something like that, but that didn't work either.

Any suggestions?

[Bob Phillips]

I do not understand what you mean by ... cells with text wrapping ...

Can you clarify, maybe post your workbook?

[kwibis]

As you can see, the last row on the printable paper is different due to different rowheight (rowheight changes with textwrapping)
http:\\imgffs.com/i/RnrXl.jpg (swap \\ to //)

[Bob Phillips]

That doesn't explain anything to me, lastrow has nothing to do with row height.

[kwibis]

I mean, looking at the dotted line, the last row is 46 (on the left) and 59 on the right.

http:\\imgffs.com/i/bNmHk.jpg the yellow part needs to be placed at the bottom of the page.

[Bob Phillips]

I am still not seeing it, there is just one image there, not a left and a right.

But, if you are worrying about invoices overflowing a page, then you have to manage it. You could do some fancy coding to calculate the height of each row and work out where to split the page, but I would think a better way is to split the invoice into 2, a first single page that summarises the work and the charges, and then a multi-page detailed list of tasks and effort/costs.

[kwibis]

No that's not the problem. If I would fill cells with text/number and stop at the dotted line (which is the end of the page), then this code:

Sheets("Offerte").UsedRange.Rows.Count

would return me a number that is equal to the number of rows that are on one A4-size page. That is the number I need.

However, now these cells are empty from 47 to 65(http:\\imgffs.com/i/bNmHk.jpg). Thus the code will return 46 instead of 65. Now I want a piece of code that returns the number of rows on the page and not just the used cells.

[Bob Phillips]

I think I understand, does this do it

[vba]
Sheets("Offerte").Cells(Sheets("Offerte").Rows.Count,"A").End(xlUp).Row[/vba]

[kwibis]

No, it returns the same as:

[VBA]Sheets("Offerte").UsedRange.Rows.Count[/VBA]

[Bob Phillips]

Really? Can you post the workbook so I can see why?

[kwibis]

I made a small workbook. But it has the same on the actual workbook.

http://db.tt/gmSRdeOI

[Bob Phillips]

You are really confusing me, what is that showing? Both methods show 13, the actual last row. I thought you were saying there were some blank rows at the end that were being counted? Not in this example.

[shrivallabha]

I guess he is referring to the last line which "Print Preview" shows. Dotted line (DisplayPageBreaks in VBA) that indicates the extent that will be printed in one page.

I don't know if it can be accessed via VBA.

[kwibis]

Dear shrivallabha,

That's exactly what I mean. Found a solution on a dutch site.
[VBA]Sheets("Offerte").HPageBreaks(1).Location.Row[/VBA]
This gives me the answer I was looking for. Thanks for your time xld!