How to find the last row on a worksheet's printed page

[BobBridges]

I've got an odd and rather picky issue. I'm exporting worksheets as PDFs, using the ExportAsFixedFormat method with Type:=xlTypePDF. The page has a corporate color as the background, and I want that color to go all the way to the bottom of the last printed page, rather than stop at the last row of the worksheet's data. I can accomplish this by putting some data in the last row of that page—just a space will do.

But how do I identify the worksheet row that will be the last row of the printed document? I can estimate it, but if there's a way to find it via a method or property, I'd rather do that. Anyone have any bright ideas?

Let me emphasize: Finding the last row of the data is trivial. But if that last row is only a few inches down from the top of the last page, it isn't what I want: I want the row that would be at the bottom of that page. So I have to identify the last page of the document (if possible), and then identify the worksheet row that will be at the bottom of that page (if possible).

 

[vletm]

BobBridges​

Do those sheets have fixed height of rows?
You can eg manually test - how many rows can show per page?
... and set print are as needed.
If there needs to 'print' many pages then You can eg add needed rows between pages while 'printing'.

 

[BobBridges]

There's a graphic containing the company logo at the top of the first page, so to keep it from covering up the first few rows of data I insert a few rows at the top after populating the sheet with data and copying in the graphic. But I'm not modifying the height of the rows, no. That is, not until the end, when I find the last row and add enough to its height to bring it to the bottom of the page—which is the goal of this post.

Yeah, I could have carefully counted the number of rows that take up a full page. What I did instead is just estimate a page as 60 rows. So far it seems to be working, but I'm keeping an eye on it. What puzzles me a little is that I had that spacing working in September, and some time between then and this past week something changed. I didn't change anything in that VBA module. Maybe Excel suffered an update.