PDA

View Full Version : Get last row of a page



kwibis
03-07-2013, 01:35 AM
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
03-07-2013, 01:39 AM
I do not understand what you mean by ... cells with text wrapping ...

Can you clarify, maybe post your workbook?

kwibis
03-07-2013, 01:51 AM
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
03-07-2013, 02:02 AM
That doesn't explain anything to me, lastrow has nothing to do with row height.

kwibis
03-07-2013, 02:19 AM
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
03-07-2013, 03:01 AM
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
03-07-2013, 03:14 AM
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
03-07-2013, 03:41 AM
I think I understand, does this do it


Sheets("Offerte").Cells(Sheets("Offerte").Rows.Count,"A").End(xlUp).Row

kwibis
03-07-2013, 03:56 AM
No, it returns the same as:

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

Bob Phillips
03-07-2013, 04:00 AM
Really? Can you post the workbook so I can see why?

kwibis
03-07-2013, 04:52 AM
I made a small workbook. But it has the same on the actual workbook.

http://db.tt/gmSRdeOI

Bob Phillips
03-07-2013, 08:03 AM
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
03-07-2013, 10:22 AM
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
03-07-2013, 10:51 AM
Dear shrivallabha,

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