PDA

View Full Version : Sleeper: Print Macro



psuguy67
02-12-2005, 04:59 PM
I have an area on a worksheet that contains external data brought in
from SQL Server using a stored procedure. The amount of rows will depend
on the query results. So I want to put a button on the worksheet that
will only print out the amount of pages of data. Is there a way that the
macro can check how many rows and set the page break to the last row of
data?

I need this to work in Excel 2000 or greater.

Thanks in advance for any help.

Regards
psuguy67

:beerchug:

Jacob Hilderbrand
02-12-2005, 05:35 PM
Try something like this.


Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = "A1:J" & LastRow

psuguy67
02-12-2005, 05:52 PM
this works, but with a small problem:

the range has empty cells in the print area because there are cell references there to another sheet with no data.

I want these to be eliminated from the print area.


I will try and explain myself a little better, I apologize if this is not clear. I have two data tabs. One data tab pulls in all the data from the database and the tab that the user is printing has the cell references to the data the user wants to see. Problem is some of these cell references pull nothing because of the lack of data, but when the user goes to print it prints out all the cells that have the references in them.

Does this make sense?

Thanks again.
Regards,
psuguy67

Jacob Hilderbrand
02-12-2005, 06:01 PM
Try this. Put the following code in the ThisWorkbook code module.


Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim LastRow As Long
Dim i As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Cancel = True
LastRow = Range("A65536").End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = "A1:J" & LastRow
For i = 1 To LastRow
If Range("A" & i).Value = "" Then
Range("A" & i).EntireRow.Hidden = True
End If
Next i
ActiveSheet.PrintOut
ActiveSheet.Cells.EntireRow.Hidden = False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

psuguy67
02-12-2005, 09:02 PM
this is not working for me......i tried figuring it out and no luck. would if help if I posted a sample?

See attached for a screenshot sample.