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