Print Visible Data Only

[Guest]

Hi,

I am currently producing a worksheet which presents the number of
outstanding errors that are produced by a group of around 200 people. The
data is in a pivot table, but this is hidden as the layout is too rigid in a
pivot table. From the drop down I can select any area of the England which is
linked to every person in the dataset, so as a result the number of rows
shown can vary from area to area. So conditional formating and formulas have
created on every cell even 300 or so blank ones, in case new people are added
to the dataset.

The formula is present as it either selects the data from the pivot table,
or shows a blank if there is no data.
The conditional formatting is present as there is a traffic light system
depending on the number of errors by each person in the dataset.

In addition to this there formatting to the cells showing change so that the
'+' sign appears when a change in the total from a previous period is upward,
and a '-' sign appears when the change is downward and finally a blank before
the zero if there is no change.

So, my problem is that I wish to only print the visible area every time the
area chosen is changed via a macro, as people other than myself (and these
people may not be so computer literate) will be using this and I would like
this feature for their convienence. But due to the fact that I have put
formulas in the blank rows below, a simple 'set print area' with 'shift',
'ctrl' and 'end' to select the print area won't work.

I hope I have explained my situation throughly enough and I hope you can
help me on this.

Many Thanks,

Mr Patel

 

[Bernie Deitrick]

Mr Patel,

If your formulas return a value of "" when the row is not used, you could use a macro, along the
lines of

Sub HideRows()
Dim myCell As Range
Range("A1:A500").EntireRow.Hidden = False
For Each myCell In Range("A1:A500")
If myCell.Value = "" Then
myCell.EntireRow.Hidden = True
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Mr Deitrick,

Apologies for the misconduct, your advice worked a treat, but it appears i'm
having a problem to select and set a print area (that covers the visible data
only) that could apply for any number of rows. Any suggestions would be
gratefully received.

Regards,

Mr Patel

 

[Guest]

Hi again,

Ignore the below, i've just figure out how to do this myself. Many thanks
for your help, it is very much appreciated.

Thanks,

Mitesh