Conditional Print Area

[hooperman]

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 Poulsom]

Try eg:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("A1:AZ" & .Range("AY65536").End(xlUp).Row)
        .PageSetup.PrintArea = Rng.Address
    End With
End Sub

Change the sheet reference to suit.

 

[hooperman]

Thanks for the reply.

I have tried this code but it doesn't seem to do anything. Do I have to set print area for A1 to AZ2002 first?
At the moment, my sheet only has data showing up to row 56 - the rest is blanked but still with formulas in. I would like a macro to just print the first 56 rows. Problem is the number of rows showing varies.

thanks

 

[hooperman]

Sorry, I tell a lie - the code does work but I still have a problem.
To get the data in column AY, I have copied and paste special values from another sheet.
To get your code to work, I had to press delete on the cells with no data in even though they had no formula in them.

 

[hooperman]

Andrew Poulsom,
I have looked further.
The cells that I thought were blank have actually got "" in them.
How do I amend you code to reflect this?

thanks

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("AY1:AY" & .Range("AY65536").End(xlUp).Row)
        Rng.AutoFilter Field:=1, Criteria1:="<>"
        LastRow = Rng.SpecialCells(xlCellTypeVisible).Rows.Count
        Rng.AutoFilter
        Set Rng = .Range("A1:AZ" & LastRow)
        .PageSetup.PrintArea = Rng.Address
    End With
    Application.ScreenUpdating = True
End Sub

 

[hooperman]

Cheers Andrew,

unfortunately, this doesn't work - the print area is blank.

 

[Andrew Poulsom]

Cheers Andrew,

unfortunately, this doesn't work - the print area is blank.

It worked when I tried it. Post a sample of what you have in column AY.

 

[hooperman]

Here is a sample.
From AY57 down to AY2002, the cells have "" in them.

ivor.xls
	AR	AS	AT	AU	AV	AW	AX	AY
53	0	0	0	0	0	8	46	46
54	0	0	0	0	0	6	25	25
55	0	0	0	0	0	7	36	36
56	0	0	0	0	0	11	53	53
57	0	0	0	0	0		2
58	0	0	0	0	0		53
59	0	0	0	0	0		49
60	0	0	0	0	0		0
61	0	0	0	0	0		0
62	0	0	0	0	0		9
63	0	0	0	0	0		0
64	0	0	0	0	0		8
WCENTRE

 

[Andrew Poulsom]

Given the data you posted, and assuming there are entries other than "" in AY1:AY52, this:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Set Sh = Worksheets("WCENTRE")
    With Sh
        Set Rng = .Range("AY1:AY" & .Range("AY65536").End(xlUp).Row)
        Rng.AutoFilter Field:=1, Criteria1:="<>"
        LastRow = Rng.SpecialCells(xlCellTypeVisible).Rows.Count
        Rng.AutoFilter
        Set Rng = .Range("A1:AZ" & LastRow)
        .PageSetup.PrintArea = Rng.Address
    End With
    Application.ScreenUpdating = True
End Sub

set the Print_Area to =WCENTRE!$A$1:$AZ$56.