Seeking Method to set conditional page breaks

[auto.pilot]

I spent considerable time searching and testing, but this has me stumped.

Using XL 2010. My headers start in row 6 and I have perhaps 500 rows of data. Periodically (every 3 to 15 rows), the word 'Sub-Total' is displayed in column F. There are also numbers and some blank cells in column F. I would like to set a page break such that no more than 30 rows of data are shown on any one page and 'Sub-Total' will be at the bottom of each page. However, I don't want a page break at every instance of 'Sub-Total' because it would result in some pages having only two rows of data. IE: There may be several 'Sub-Totals' on each page because they term appears every 3 to 15 rows.

I would ideally like to post a sample of my data, but can't do that using my office PC.

Would appreciate any help.

Thanks in advance.

Jim

 

[auto.pilot]

I found a way to provide a sample of my data below. I would like to automatically set up page breaks so that none of the groupings shown are split across more than one printed page. IE: More than one group can show on a page, but the 'Sub-Total' at the bottom of each page needs to be a full group, not partial. The start of the next printed page should be the start of a new group.

Tough to describe.... hoping that a VBA solution is possible.

Would appreciate any thoughts.

Thanks

Excel Workbook
	A	B	C	D	E	F	G
6	Date1	Date2	Client #	Rate	Terms	Region	Amount
7	4/26/2016	12/4/2015	1409	5.0%	82	1	934,631
8	6/28/2015	11/27/2015	1409	5.0%	35	1	960,010
9	12/7/2015	7/14/2015	1409	7.0%	52	1	792,475
10						Sub-Total	2,687,116
11
12	11/14/2015	7/21/2015	4321	9.0%	65	2	662,442
13	5/10/2016	6/22/2015	4321	6.0%	67	2	445,541
14	6/17/2015	4/20/2016	4321	5.0%	44	2	596,937
15	8/25/2015	12/21/2015	4321	9.0%	65	2	831,715
16	2/16/2016	8/21/2015	4321	6.0%	88	2	844,167
17						Sub-Total	3,380,802
18
19	3/7/2016	12/15/2015	3856	7.0%	31	1	171,711
20						Sub-Total	171,711
21
22	4/9/2016	1/24/2016	4338	9.0%	88	3	367,496
23	12/7/2015	9/10/2015	4338	5.0%	31	3	912,338
24	7/20/2015	2/4/2016	4338	7.0%	70	3	358,774
25						Sub-Total	1,638,608
26
27	9/8/2015	5/14/2016	4296	9.0%	31	1	574,064
28	2/3/2016	12/16/2015	4296	8.0%	61	1	525,236
29						Sub-Total	1,099,300
30
31	12/13/2015	11/24/2015	3694	9.0%	37	2	637,005
32						Sub-Total	637,005
33
34	7/27/2015	11/7/2015	2067	9.0%	82	2	689,754
35	7/2/2015	10/11/2015	2067	5.0%	43	2	756,964
36	11/5/2015	12/26/2015	2067	6.0%	88	2	613,790
37	5/31/2016	8/9/2015	2067	5.0%	74	2	139,574
38	2/10/2016	5/31/2016	2067	8.0%	70	2	934,012
39						Sub-Total	3,134,094
40
41	8/21/2015	10/28/2015	1988	9.0%	64	1	939,967
42	11/9/2015	1/4/2016	1988	5.0%	67	1	648,615
43						Sub-Total	1,588,582
44
45	8/13/2015	11/25/2015	3598	9.0%	41	3	573,050
46	11/24/2015	3/7/2016	3598	7.0%	40	3	445,125
47						Sub-Total	1,018,175
Sheet1