Sleeper: Print Macro

[psuguy67]

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

[Jacob Hilderbrand]

Try something like this.

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

[psuguy67]

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]

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]

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.