I have a sheet which can have anything between approx 50 or 100 rows of data,
controlled by formulae which will leave the cells blank where no data is
required. I want to print it all on one page but am having problems with
leaving large blank areas at the bottom of the page if a low number of rows
are shown.

Rather than always setting the print area to the maximum that will ever be
required I want my macro to set the print area to show only the non-blank
rows. As some cells within the range are blank anyway I can't simply count
the non-blank cells and do it that way.

Any ideas? (I'm not very good at programming so if you could explain any
solutions I would be very grateful!)

Thanks

Phil

Hi Phil,

These three lines of code put an autofilter on the used range, filters
the first column for non-blank values, prints the visible values and
turns the autofilter off again.

ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:="<"
ActiveSheet.PrintOut
ActiveSheet.UsedRange.AutoFilter

With kind regards,
Ton Teuns

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Ton

Unfortunately this won't work as some required rows have a blank first cell.
There are no columns which will always be non-blank in all the required rows
so can't use this method.

Thanks anyway.
Phil

"ton teuns" wrote:

Hi Phil,

These three lines of code put an autofilter on the used range, filters
the first column for non-blank values, prints the visible values and
turns the autofilter off again.

ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:="<"
ActiveSheet.PrintOut
ActiveSheet.UsedRange.AutoFilter

With kind regards,
Ton Teuns

*** Sent via Developersdex http://www.developersdex.com ***

Just realised I can insert a new column A with white text, use that to check
the whole row for non-blank cells and filter on that - works a treat!

Thanks!

"Defoes Right Boot" wrote:

Thanks Ton

Unfortunately this won't work as some required rows have a blank first cell.
There are no columns which will always be non-blank in all the required rows
so can't use this method.

Thanks anyway.
Phil

"ton teuns" wrote:

Hi Phil,

These three lines of code put an autofilter on the used range, filters
the first column for non-blank values, prints the visible values and
turns the autofilter off again.

ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:="<"
ActiveSheet.PrintOut
ActiveSheet.UsedRange.AutoFilter

With kind regards,
Ton Teuns

*** Sent via Developersdex http://www.developersdex.com ***