Vba to set a print area judging where to put the page brakes by the blank rows?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,744
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
I'm really struggling with this hope you can help,
I have arrange of EB100:EJ500 where i add details of this weeks sales etc.
I want to print out the pages are very tidy looking.
after looking at my data ive realised that the best [laces to put the page breaks would be Every 64 rows

However this gives me a problem as some of my info should be read together.

so hears what I'd like to see Id Like the macro to give me an output of Page sizes so i can check what is going on,
so this is my idea

Starting at Row 100 and Using Column EC find the words "Kind regards," and add ten to the row its in, (This gives me the final row as i often don't use the entire Range area)

If Kind Regards + 10 row is less than 164 then in cell EL100put the range "EB100:EJ164" as this is the area needed,
however if the last row is more than 164 then break it up into pages using this this rule

for each sheet need to create pages of 64 or less there must be two blank rows in column EC to add the Page break

So as an example lets say the range was EB100:EJ300
first should be EB100:EJ164
but if EC164 has data in it then go to EC 163, EC 162 and so on until you find two blank cells

please help
thanks
Tony
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Watch MrExcel Video

Forum statistics

Threads
1,129,666
Messages
5,637,649
Members
416,979
Latest member
CapeCon

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