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

RichardHell

New Member
Joined
Apr 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:

1622143671347.png


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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,763
To return the first row after the first horizontal page break, try...

VBA Code:
Worksheets("Sheet1").HPageBreaks(1).Location.Row

Change the sheet name accordingly.

Hope this helps!
 

RichardHell

New Member
Joined
Apr 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
To return the first row after the first horizontal page break, try...

VBA Code:
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:

1622151574193.png


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:

1622151974612.png


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


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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,763
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...

VBA Code:
    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
 
Solution

RichardHell

New Member
Joined
Apr 20, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Well that's a clever way of going about it lol. It works perfectly, thank you so much!
 

Forum statistics

Threads
1,144,667
Messages
5,725,669
Members
422,635
Latest member
crisis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top