Conditional Print Area

hooperman

New Member
Joined
Dec 7, 2005
Messages
9
Hello,

Hope someone can help me.
I have a large sheet with formulas in every cell from cell A1 to AZ2002. The only difference being column AY which either has a number in or nothing at all (no formula).
The data is conditionally formatted so that if there is no data in column AY, then the cells in that row are in white text (blanked out)
The whole range A1 to AZ2002 is sorted by column AY.
Therefore, the sheet is 2002 rows deep but most is blanked out but still has a formula in it. The rows still showing are all sorted to the top of the sheet.
How do I create a macro to print only the rows that are showing? This maybe 1 row or 858 rows. It needs to set the print area from cell A1 to the first blank cell in column AY.

thanks in advance.

Hooperman.
 
Andrew,

this doesn't work again. The print area is blank.

Would another way be to look at column AY in the range A1 to AZ2002 and hide every row where the cell is equal to ""?
This would leave only the rows that I am after and so would only print those rows. Unforunately, I don't have a clue how to do that so your help again would be greatly appreciated.

thanks
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I don't understand why it works for me but not for you.

AutoFiltering column AY for NonBlanks should hide the rows you don't want to print.
 
Upvote 0
Andrew,

for column AY1 to AY2002, all of the cells have something in them either a number or "". None of them are blank. If I go to cell AY6 and press END and then the down arrow, i end up at AY2002 even though only 56 rows appear to have data in them.
This is because I have used another macro to copy and paste values from another sheet. In the other sheet, cells A1 to AY2002 had formulas in every cell but I have set it so that some of the results give "".

thanks
 
Upvote 0
hooperman said:
Andrew,

for column AY1 to AY2002, all of the cells have something in them either a number or "". None of them are blank. If I go to cell AY6 and press END and then the down arrow, i end up at AY2002 even though only 56 rows appear to have data in them.
This is because I have used another macro to copy and paste values from another sheet. In the other sheet, cells A1 to AY2002 had formulas in every cell but I have set it so that some of the results give "".

thanks

Unlike the navigation keys or Edit|Go To|Special|Blanks, AutoFilter treats cells containing "" as blank. Try it before assuming otherwise.
 
Upvote 0
Andrew,

I have sorted it - my mistake.
The cells AY1 and AY2 were blank so your code just looked at rows 1 and 2. I have entered some numbers in those cells and now it works.
Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top