Insert Blank Rows (up to next page)

[Haydo]

I have tried to do this...but outside my skill range...hope someone can help.

On a worksheet containing about 20 columns
some have functions and macros

I would like this vba created so that when information contained within a race (what i call a subset) runs into the next page (for printing), rows are inserted to carry that subset to the next page, so when printed it is easier to read.

This data is in column B

Down the page. the cells read like this...

race 1 ...... (there is more data in this cell but always begins with race #)
tno (each set begins with race 1) (each race is a subset)
1
2
3
4
5

race 2 (each race is a subset)
tno
1
2
3

race 3 (each race is a subset)
tno
1
2
3
4
5
6
7

race 1 (a new set begins with race 1) (each race is a subset)
1
2
3
4
5


and so on.

after approx 8 or 9 of these races (subsets), a new race 1 (new set) is introduced, and continues in the same way until the next race 1,

There can be up to 10 sets (each containing between 6 and 10 races (subsets))

I have used Page Setup to create 56 lines on each page.

I need 2 things to happen.
(1) when information in the same race (subset) runs into the next printed page, (56 row pages ) - I would like that particular subset (from the 'race..#' cell) to move to the next page (insert rows upto the next set of 56 lines)

So a page when printed consists only of complete subsets, with no splits/carryovers onto the next page.

(2) When a new set ( "race 1 ...) begins on the same page as the previous subset (eg race 8 of previous subset) I would like rows to be inserted so that the set (race 1) begins on a new page.

So it would look like this....

race 8
tno
1
2
3



(rows inserted here)



---------------(end of page)
race 1
tno
1
2


I hope this make sense. Thanks in advance.

 

[drsarao]

Haydo,
Adding rows is not essential (it isn't elegant either). A page break does the same thing nicely.
eg your second requirement can be met with:

Sub InsHPgBk()
lr = Cells(Rows.Count, "A").End(xlUp).Row   'last populated row
col1 = Range("A1:A" & lr)
For i = 5 To lr 'start from 5th row so as to skip the first "race 1"
    If Left(col1(i, 1), 6) = "race 1" Then
        ActiveSheet.HPageBreaks.Add before:=Range("A" & i)
    End If
Next i
End Sub

 

[Haydo]

Haydo,
Adding rows is not essential (it isn't elegant either). A page break does the same thing nicely.
eg your second requirement can be met with:

Sub InsHPgBk()
lr = Cells(Rows.Count, "A").End(xlUp).Row   'last populated row
col1 = Range("A1:A" & lr)
For i = 5 To lr 'start from 5th row so as to skip the first "race 1"
    If Left(col1(i, 1), 6) = "race 1" Then
        ActiveSheet.HPageBreaks.Add before:=Range("A" & i)
    End If
Next i
End Sub

Thanks for the page break - break through! Much easier.

This code is not working....there is no change at all on the worksheet.

Not sure what the ....'For i = 5 To lr 'start from 5th row so as to skip the first "race 1" .... is about.

is the 5th row relavent? - I'm guessing.

Do i need to do something else to get it working?

 

[Haydo]

perhaps i did not make it clear - the ascending numbers tht run down column B in each subset go to irregular numbers. Some to 5, some to as high at 24.

 

[drsarao]

Re: Insert Page Breaks

It works for me.
Try copy-paste again.
Preferably in a new module.
5th row is not critical in "i=5 to lr" This is basically to avoid a page break on the first race set.

 

[Haydo]

I am using a different vba on this same worksheet, which inserts a row at the end of each subset.


Sub Insert_Rows() Dim rng As Range Application.ScreenUpdating = False Range("B1", Range("B" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:="Race*" Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) ActiveSheet.AutoFilterMode = False rng.EntireRow.Insert Application.ScreenUpdating = True End Sub</pre>
I cannot get the page break code to work at all.

Is there anything in the code above that allows it to work, which could be added or changed in the pagebreak code?

Thanks

 

[Haydo]

This is the code...readable this time.

Sub Insert_Rows()
    Dim rng As Range
    Application.ScreenUpdating = False
    Range("B1", Range("B" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:="Race*"
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
    ActiveSheet.AutoFilterMode = False
    rng.EntireRow.Insert
    Application.ScreenUpdating = True
End Sub