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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try eg:

Code:
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

New Member
Joined
Dec 7, 2005
Messages
9
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

New Member
Joined
Dec 7, 2005
Messages
9
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

New Member
Joined
Dec 7, 2005
Messages
9

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
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

New Member
Joined
Dec 7, 2005
Messages
9

ADVERTISEMENT

Cheers Andrew,

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
hooperman said:
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

New Member
Joined
Dec 7, 2005
Messages
9
Here is a sample.
From AY57 down to AY2002, the cells have "" in them.
ivor.xls
ARASATAUAVAWAXAY
530000084646
540000062525
550000073636
5600000115353
57000002
580000053
590000049
60000000
61000000
62000009
63000000
64000008
WCENTRE
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Given the data you posted, and assuming there are entries other than "" in AY1:AY52, this:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,018
Messages
5,569,652
Members
412,286
Latest member
kychemist00
Top