Prevent Blank Pages From Printing because of formulas returning 0 and Blanks

[superman0623]

I have created an excel Document that has formulas down 6,000 Rows. The print area that I want is $AD$3001:$AI$???, I dont know what the last row will be because the spreadsheet could return as 1 page of visible information up to 70 pages of visible information. So I need to do either a VBA or a dynamic print range using the define name function, but, all of my attempts thus far has failed. Can anyone Help? I copy and paste data into A7:A2999 which the Formulas in $AD$3001:$AI$6000 pull their data from and the results in $AD$3001:$AI$6000 is what I want to print, but just the range that is visible... PLEASE HELP!!!

[superman0623]

I have attached a sample of my excel. In the sample it is only showing 1 shift instead of 3 with only one employee and I have deleted all formulas below to make the document small enough to fit. The formulas usually run from row 3001 to 6000. As you can see in this sample row AF will alaways be the lowest row as it ends with the shifts name and AG ends with the totals. The supplied sample is only based off of 1 function (CasPck1) so this is only a little over a page, but we have up to 30 functions and 120 employees which could take this up to 50+ pages, so I want to set the print area to adjust with the info. The supervisors simply paste another excel into A7 which shows them a pretty picture lol but all of their info is hidden between A7:Z3000. Thanks for your guys help!

[superman0623]

I have attached a sample of my excel. In the sample it is only showing 1 shift instead of 3 with only one employee and I have deleted all formulas below to make the document small enough to fit. The formulas usually run from row 3001 to 6000. As you can see in this sample row AF will alaways be the lowest row as it ends with the shifts name and AG ends with the totals. The supplied sample is only based off of 1 function (CasPck1) so this is only a little over a page, but we have up to 30 functions and 120 employees which could take this up to 50+ pages, so I want to set the print area to adjust with the info. The supervisors simply paste another excel into A7 which shows them a pretty picture lol but all of their info is hidden between A7:Z3000. Thanks for your guys help!

In this example that I submitted I want to capture the bottom total as well even though it does have the unwanted space due to page breaks on the excel that is posted in to generate these results. So in this example the print area would be AD3001:AI3049.

[benishiryo]

you can shorten your AE3001 formula to just:
=IFERROR(VLOOKUP(A15,'Data(1)'!A:C,3,0),"")
VLOOKUP is basically looking at A15 inside column A of Data sheet. once it found the first instance, i label 3 to extract the 3rd column from Column A, so that's column C. note that my 2nd argument in the VLOOKUP formula must include Column A where lookup_value can be found and all the way to column C where the value i want is. the last argument of VLOOKUP is a 0 for finding exact match. some people use FALSE. works the same. IFERROR helps you return a blank when the lookup_value cannot be found

you can adjust Column AB accordingly. i saw that it is taking different values and i don't wish to meddle with it.

for your dynamic range, i saw that your formula ends in row 3050 nicely. i'm wondering if it's really the case of you are trying to simplify things. a formula for dynamic range is extremely sensitive, so these tiny little details matter a lot. if it ends nicely 1 row after where you should print, then press CTRL + F3 to use the Name Manager. you will see a Print_Area name. click on that and click on the Refers to. put in:
=$AD$3001:INDEX($AH:$AH,3000+COUNTA($AH$3001:$AH$10000)-1)

i indicate that my row starts from AD3001. i then make the end part a variable. by using the INDEX formula, i can indicate where i want it to end. it's supposed to be AH3049. so i need to get 3049. 3000 is a given, since it's always going to be at least 3000 rows. i then add COUNTA to count how many of the cells in AH3001:AH10000 are filled. that will give me 50 counts. 1 extra because your formula ended in 3050. so i minus 1

if it's not that straightforward, you can play around with the portion on how to get the 3049. some patterns i noted for you to play with:
1. every set of Column AE values occupies 3 rows. for eg. AE3001:AE3003. so you can use a COUNTIF to find out how many of these are more than 0:
=COUNTIF(AE3001:AE10000,">0")
there are 13 of them. since they occupy 3 rows each, multiply by 3. that gives you 39 rows.
=COUNTIF(AE3001:AE10000,">0")*3
2. from row 3040 (after the last entry) to the 1st Shift text is 10 rows. so you can use the above COUNTIF + 10. final formula would be:
=$AD$3001:INDEX($AH:$AH,3000+COUNTIF(AE3001:AE10000,">0")*3+10)
3. if you will have another set where there is like a "2nd Shift xxxx", then you would have to make the 10 rows a variable too. use COUNTIF to find out how many times "Shift" appears
=COUNTIF(AF3001:AF10000,"*shift*")
similar to the other COUNTIF, this one occupies 10 rows each time it appears. so multiply by 10
=COUNTIF(AF3001:AF10000,"*shift*")*10
final formula:
=$AD$3001:INDEX($AH:$AH,3000+COUNTIF(AE3001:AE10000,">0")*3+COUNTIF(AF3001:AF10000,"*shift*")*10)

as you can see, there are too many factors. and since i made the effort to explain to you, i hope you can make the effort to figure it out

[superman0623]

you can shorten your AE3001 formula to just:
=IFERROR(VLOOKUP(A15,'Data(1)'!A:C,3,0),"")
VLOOKUP is basically looking at A15 inside column A of Data sheet. once it found the first instance, i label 3 to extract the 3rd column from Column A, so that's column C. note that my 2nd argument in the VLOOKUP formula must include Column A where lookup_value can be found and all the way to column C where the value i want is. the last argument of VLOOKUP is a 0 for finding exact match. some people use FALSE. works the same. IFERROR helps you return a blank when the lookup_value cannot be found

you can adjust Column AB accordingly. i saw that it is taking different values and i don't wish to meddle with it.

for your dynamic range, i saw that your formula ends in row 3050 nicely. i'm wondering if it's really the case of you are trying to simplify things. a formula for dynamic range is extremely sensitive, so these tiny little details matter a lot. if it ends nicely 1 row after where you should print, then press CTRL + F3 to use the Name Manager. you will see a Print_Area name. click on that and click on the Refers to. put in:
=$AD$3001:INDEX($AH:$AH,3000+COUNTA($AH$3001:$AH$10000)-1)

i indicate that my row starts from AD3001. i then make the end part a variable. by using the INDEX formula, i can indicate where i want it to end. it's supposed to be AH3049. so i need to get 3049. 3000 is a given, since it's always going to be at least 3000 rows. i then add COUNTA to count how many of the cells in AH3001:AH10000 are filled. that will give me 50 counts. 1 extra because your formula ended in 3050. so i minus 1

if it's not that straightforward, you can play around with the portion on how to get the 3049. some patterns i noted for you to play with:
1. every set of Column AE values occupies 3 rows. for eg. AE3001:AE3003. so you can use a COUNTIF to find out how many of these are more than 0:
=COUNTIF(AE3001:AE10000,">0")
there are 13 of them. since they occupy 3 rows each, multiply by 3. that gives you 39 rows.
=COUNTIF(AE3001:AE10000,">0")*3
2. from row 3040 (after the last entry) to the 1st Shift text is 10 rows. so you can use the above COUNTIF + 10. final formula would be:
=$AD$3001:INDEX($AH:$AH,3000+COUNTIF(AE3001:AE10000,">0")*3+10)
3. if you will have another set where there is like a "2nd Shift xxxx", then you would have to make the 10 rows a variable too. use COUNTIF to find out how many times "Shift" appears
=COUNTIF(AF3001:AF10000,"*shift*")
similar to the other COUNTIF, this one occupies 10 rows each time it appears. so multiply by 10
=COUNTIF(AF3001:AF10000,"*shift*")*10
final formula:
=$AD$3001:INDEX($AH:$AH,3000+COUNTIF(AE3001:AE10000,">0")*3+COUNTIF(AF3001:AF10000,"*shift*")*10)

as you can see, there are too many factors. and since i made the effort to explain to you, i hope you can make the effort to figure it out

Wow, thank you for all of the great information. I have tried the AD change and it is working like a charm (Cant believe I took such a long way), I will continue on with the rest later tonight and tomorrow and let you know. once again thank you so much.