Limiing extent of printout

[Chris Watts]

I have a vba macro that creates a report in the form of a sheet. This sheet
is of varibale length but typically between 2 and 5 pages long and 1 page
wide when printed.
When I do try to print or print preview it then Excel tells me that it
consists of some 600 - 700 pages! Clearly with a variable length sheet I
cannot simply use print area - so how can I limit the area that it tries to
print out to that which actual contains data?

TIA
cheers
Chris

 

[Dave Peterson]

Usually excel can determine what area you've used and print just that.

If you hit ctrl-end, do you go way past what you think is the bottom right
corner of the used range?

If you do, you can reset that last used cell by some of the techniques at Debra
Dalgleish's site:
http://contextures.com/xlfaqApp.html#Unused

You could always have the Before_Print event determine the extent to print.

But you'd have to share more details.

What columns should be printed?

Can you pick out a column that always has data in it if that row should be
printed--this could be used to determine the last row to print.

Remember that formulas that evaluate to "" still count as having something in
it.

 

[Chris Watts]

Thanks Dave,
I opted to use the following which works for me:

Sub PrintReport()
Dim iLastRow As Integer

iLastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Consistancy Report").Range("A1:M" & iLastRow).PrintPreview

End Sub


cheers
Chris

 

[Dave Peterson]

Depending on how big your data grows (in rows), you may want to use:

Dim iLastRow as Long

In fact, I wouldn't use "as integer" for any declaration.