VBA to insert page breaks at a blank with varying blck size

[Simon B]

Hi
I've written a macro that takes a lot of data and summarises it into blocks of data one page wide, and multiple pages long. Each block of data in the output is always the same length, but depending on where I extract the data from the length may be different in each run through, so depending on this each block may be 5 or 10 or 15 rows, in fact any number. Each block is separated by a blank cell in column B. What I want to do is put a page break in at the blank cell above where the 'natural' page break is, so that the blocks of data are not split over 2 pages. I have tried to write this and had a look on forum sites and have so far been unsuccessful, so any help anyone has would be greatly appreciated.
Thanks
Simon

 

[Simon B]

Spoke too soon. I tried it on a sheet that has 4 pages rather than just 2 and it now falls over at
Pdiv = ActiveSheet.HPageBreaks(PgCt).Location.Row
with a Run Time Error 9, Subscript out of range.

 

[Simon B]

That worked and then fell over on more than 2 pages. Have got the Mikes original code to work by bracketing it inbetween
ActiveWindow.View = xlPageBreakPreview
Code
ActiveWindow.View = xlNormalView
Don't really understand but it works now.
Cheers Mike

 

[ukmikeb]

Simon

Oops , my bad there I should have told you that the Active Window had to be in Page Break Preview.

However, I have cut the looping mechanism to a one liner, as follows :-

Remove this section of code and the Dim of Pdiv :-

Pdiv = ActiveSheet.HPageBreaks(PgCt).Location.Row
    
If Not IsEmpty(Range("B" & Pdiv).Value) Then
'   Cell is not empty therefore find the first occurrence of blank in Col B above this row
        Do
'          Loopback until there is an empty cell in Col B
            Pdiv = Pdiv - 1
        Loop Until IsEmpty(Range("B" & Pdiv))
'   Set the new Page break above the empty cell
    Set ActiveSheet.HPageBreaks(PgCt).Location = Range("B" & Pdiv)
    End If

and replace with this code :-

 Dim Blkrw as Long
If Not IsEmpty(Range("B" & ActiveSheet.HPageBreaks(PgCt).Location.Row)) Then
'   Cell is not empty therefore find the beginning of this block of data (blank in Col B above found row)
        Blkrw = Range("B" & ActiveSheet.HPageBreaks(PgCt).Location.Row).End(xlUp).Row
'   Set the new Page break above the empty cell
    Set ActiveSheet.HPageBreaks(PgCt).Location = Range("B" & Blkrw - 1)
    End If

hth

 

[Simon B]

Cheers Mike
No problem, I got it working in the end which is the main thing. Nice new short bit of code to do it too. Think that should be my next project, learn how to be concise in VBA, I get things working, but I'm always sure there must be a slicker & quicker way of doing it.
Have a good weekend.
Simon

 

[ukmikeb]

Simon

I have been using the technique in my project for quite a while now except that I concentrated on having the page break either on or below the blank row.

In this instance the path to being concise arose out of chance.

I noted one of the MVPs commenting in another thread where the object was to make the code shorter. "If it ain't broke don't fix it - shorter is not always better"!

Pleased to have helped you solve your problem.

Good luck with your project.

Have a nice weekend too.

 

[Johngeology]

I have basically the exact situation as the original poster. My slight "tweak " that I do not know how to accomplish is the following. I have four sets of blocks of data. Each block has a row name to start the block and a row name to end the block. I would love to send the row that starts a block to the next page IF the start row and end row are separated by a natural page break.
I hope that makes sense.
Thank you in advance

 

[ukmikeb]

Hi

Welcome to the MrExcel forum.

If you follow the replacement code in Post #13 you can :-
1, determine which row the page break is - the if statement
2, using your start block row and end block row you can determine whether the page break is in the block
3, if the page break is in the block you can set the page break location to be the row before the block starts - modifying the line before the End If statement.
4, if the page break is outside a block, do nothing.

Do come back if you still have a problem with a sample of your data (see the bottom of my signature) and qualify what "Each block has a row name to start the block and a row name to end the block." means, ie does it mean that they are defined Range names?

hth

 

[Johngeology]

Thanks for the reply, I'll try.
its specific names on those rows. "Form A other" would be the start row of one of the blocks everytime. That block would always end with a row called "Form A other total". The next three blocks are the same just has a "b", "c", or "d" instead of the "A".
thanks again.

 

[JAWHARRAH]

Simon

Oops , my bad there I should have told you that the Active Window had to be in Page Break Preview.

However, I have cut the looping mechanism to a one liner, as follows :-

Remove this section of code and the Dim of Pdiv :-

Pdiv = ActiveSheet.HPageBreaks(PgCt).Location.Row
   
If Not IsEmpty(Range("B" & Pdiv).Value) Then
'   Cell is not empty therefore find the first occurrence of blank in Col B above this row
        Do
'          Loopback until there is an empty cell in Col B
            Pdiv = Pdiv - 1
        Loop Until IsEmpty(Range("B" & Pdiv))
'   Set the new Page break above the empty cell
    Set ActiveSheet.HPageBreaks(PgCt).Location = Range("B" & Pdiv)
    End If

and replace with this code :-

Dim Blkrw as Long
If Not IsEmpty(Range("B" & ActiveSheet.HPageBreaks(PgCt).Location.Row)) Then
'   Cell is not empty therefore find the beginning of this block of data (blank in Col B above found row)
        Blkrw = Range("B" & ActiveSheet.HPageBreaks(PgCt).Location.Row).End(xlUp).Row
'   Set the new Page break above the empty cell
    Set ActiveSheet.HPageBreaks(PgCt).Location = Range("B" & Blkrw - 1)
    End If

hth

Hi Mike, I was looking for such a long time for help with a page break. Your code has saved me big time! Thank you so much

I am wondering if you can help me by going one step further with this code?

I have a lot of formulas that are hidden, so when I use this code it works how I want it to work, however it does gives me a page break for blank pages.

I don't want the blank pages printed, are you able to assist?

Thanks in advance
Jo

 

[ukmikeb]

Jo

Pleased to hear that you found the code useful.

It's not clear whether your hidden formulae produce rows which have values or blank rows.

I would suggest that the best option is prior to formatting the page breaks to add code which would filter and subsequently hide the blank rows.

Then having printed the report revert the hidden rows.

hth