Go to next blank page in page layout view / find value of first row in blank page

[RichardHell]

I have a template that users can fill with data. I want to be able to paste that template on the next available blank page. All I need to know is how to get the value of the first row of that empty page with vba. Example:

I expect the users to mess around with row heights and the like, so I need to find the value of the row dynamically. Already tried looking for page breaks and other things, but couldn't quite crack it. Any help would be very much appreciated.

 

[Domenic]

To return the first row after the first horizontal page break, try...

Worksheets("Sheet1").HPageBreaks(1).Location.Row

Change the sheet name accordingly.

Hope this helps!

 

[RichardHell]

To return the first row after the first horizontal page break, try...

Worksheets("Sheet1").HPageBreaks(1).Location.Row

Change the sheet name accordingly.

Hope this helps!

Hi, thanks for your answer. I already tried with something similar. The problem is that since the next page is blank, Excel doesn't create a page break, and therefore you can't get the first row of a blank page using HPageBreaks (or at least I haven't figured out how). Here are some pics to explain:

First, my template uses two pages by default. Excel considers the space between the first two pages a page break, so code like yours returns this in this case:

However, when trying to find the first row of the next page (which is blank) by changing the index in HPageBreaks, Excel can't find another page break and returns an error:

I assume this means that there are no more page breaks other than the first, which is confirmed by running .HPageBreaks.Count:

So unfortunately I still have the same problem. Still grateful for the help though!

 

[Domenic]

It looks like the only way it will recognize a page break is when the next page contains data. So maybe we can cheat a bit. We can enter some temporary data in Column A, get the row number for the next page, and then remove the temporary data. So, for example, assuming that the worksheet containing the data is the active sheet, and that your data will never exceed 100,000 rows (of course, you can reduce or increase the number of rows accordingly), you can get the first row from the third page as follows...

    Dim nextRow As Long
    nextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    With Range(Cells(nextRow, "A"), "A100000")
        .Value = "x"
        
        Dim thirdPageFirstRow As Long
        thirdPageFirstRow = ActiveSheet.HPageBreaks(2).Location.Row
        
        .Value = ""
    End With
    
    MsgBox "The third page first row is " & thirdPageFirstRow & ".", vbInformation

 

[RichardHell]

Well that's a clever way of going about it lol. It works perfectly, thank you so much!

 

[Domenic]

You're very welcome, glad I could help.

Cheers!