Define a range of rows to search for automatic page break

[djclifton]

I am fairly new to Macros and have a problem that I think can be easily solved but need some experience to help.

The task: 1. Search a range of rows to determine if an automatic page break occurs. 2. If yes, place a manual page break at before the first row in the range. 3. Repeat task for other ranges or groups of rows.

I have written code that works but is fairly long and is going to take me along time to write considering that amount of groups of rows that I have. The code for 1 group looks like this;

If Rows(54).PageBreak = xlPageBreakAutomatic Or Rows(55).PageBreak = xlPageBreakAutomatic Or Rows(56).PageBreak = xlPageBreakAutomatic Then
Rows(53).PageBreak = xlPageBreakManual
End If

I have tried to rewrite the code like this but it only searches the first row in the range (in this case row 54);

If Rows("54:56").PageBreak = xlPageBreakAutomatic Then
'Rows(53).PageBreak = xlPageBreakManual
End If

Can any one tell me what I am doing wrong?

 

[Boller]

I think this is probably what you're looking for:
http://www.mrexcel.com/forum/excel-...sert-page-breaks-blank-varying-blck-size.html

 

[djclifton]

Thanks Boller however I have looked at that post and it is not going to work for me in that I have varying size groups, without blank cells between them, that are sometimes hidden and sometimes shown.

Basically I have around 30 groups of rows all of different sizes that are sometimes shown and sometimes hidden (based on the results of a questionnaire on another sheet). I want to fill as much data on a page as I can but still keeping the groups of rows together.

The initial code that I have written works perfectly but I was hoping to be able to write more efficient code that analyzes each group of data for an automatic page break instead of each individual row in the group.

 

[Boller]

But you need to be able to identify each group of data.
For example, do you have a column where the data changes at the start of a new group?

 

[djclifton]

I do but the cells are merged in those particular columns. EG see image below for a sample of one group where the group spans across rows 17 - 21 and the cells in column A are merged for all of the rows within the group. The data changes for each group in column A.

I was going to define each group which is no bother but wanted to define each group with something like the following code that analizes all rows within the group, not just the first row only as this code does...

If Rows("18:21").PageBreak = xlPageBreakAutomatic Then
'Rows(17).PageBreak = xlPageBreakManual
End If

 

[djclifton]

Sorry my image didn't work. Try this...

 

[djclifton]

Sorry, cant get my local image onto this forum. I'm terrible at this...

 

[djclifton]

And my code didn't display in the code box either...

[COLOR=#333333]If Rows("18:21").PageBreak = xlPageBreakAutomatic Then[/COLOR]
[COLOR=#333333]'Rows(17).PageBreak = xlPageBreakManual[/COLOR]
[COLOR=#333333]End If[/COLOR]

 

[Boller]

If it's just a question of revising the code you posted, then:

Dim rw As Range
For Each rw In Rows("54:56")
    If Rows(rw.Row).PageBreak = xlPageBreakAutomatic Then
        Rows(53).PageBreak = xlPageBreakManual
        Exit For
    End If
Next

 

[djclifton]

That was exactly what I was after Boller. Thanks a million! (again!)