Seeking Method to set conditional page breaks

auto.pilot

Well-known Member
Joined
Sep 27, 2007
Messages
711
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

auto.pilot

Well-known Member
Joined
Sep 27, 2007
Messages
711
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
ABCDEFG
6Date1Date2Client #RateTermsRegionAmount
74/26/201612/4/201514095.0%821 934,631
86/28/201511/27/201514095.0%351 960,010
912/7/20157/14/201514097.0%521 792,475
10Sub-Total 2,687,116
11
1211/14/20157/21/201543219.0%652 662,442
135/10/20166/22/201543216.0%672 445,541
146/17/20154/20/201643215.0%442 596,937
158/25/201512/21/201543219.0%652 831,715
162/16/20168/21/201543216.0%882 844,167
17Sub-Total 3,380,802
18
193/7/201612/15/201538567.0%311 171,711
20Sub-Total 171,711
21
224/9/20161/24/201643389.0%883 367,496
2312/7/20159/10/201543385.0%313 912,338
247/20/20152/4/201643387.0%703 358,774
25Sub-Total 1,638,608
26
279/8/20155/14/201642969.0%311 574,064
282/3/201612/16/201542968.0%611 525,236
29Sub-Total 1,099,300
30
3112/13/201511/24/201536949.0%372 637,005
32Sub-Total 637,005
33
347/27/201511/7/201520679.0%822 689,754
357/2/201510/11/201520675.0%432 756,964
3611/5/201512/26/201520676.0%882 613,790
375/31/20168/9/201520675.0%742 139,574
382/10/20165/31/201620678.0%702 934,012
39Sub-Total 3,134,094
40
418/21/201510/28/201519889.0%641 939,967
4211/9/20151/4/201619885.0%671 648,615
43Sub-Total 1,588,582
44
458/13/201511/25/201535989.0%413 573,050
4611/24/20153/7/201635987.0%403 445,125
47Sub-Total 1,018,175
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,734
Members
414,170
Latest member
Mdm

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