Set print range with VBA

[tuxalot]

Using the before print routine in Excel 02, I am trying to set the print range to contain cells with data, and exclude those that are blank. I have found several useful threads on how to determine the last used cell in a worksheet, but the worksheet in question is a (3) page template and depending on user input, may have several rows blank. The blank rows have conditional formatting, but no data. Using the above technique, Excel selects the last cell in the template, even when the cell (or row) is empty.

Any help would be greatly appreciated.

Darin

 

[BrainB]

This should work OK :-

'-----------------------------------------------------------
'- macro to hide empty rows in the Active Sheet
'- and Print
'-----------------------------------------------------------
Sub HIDE_BLANK_ROWS()
    Dim LastRow As Long
    Dim CheckRow As Range
    '--------------------
    LastRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
                SearchOrder:=xlByRows).Row
    For r = 1 To LastRow
        Set CheckRow = ActiveSheet.Rows(r)
        x = Application.WorksheetFunction.CountA(CheckRow)
        If Application.WorksheetFunction.CountA(CheckRow) = 0 Then
            CheckRow.EntireRow.Hidden = True
        End If
    Next
    ActiveSheet.PrintOut
End Sub
'----------------------------------------------------------

Regards
BrianB
** Let us know if you get something that works !
================================

 

[tuxalot]

Thanks for your help Brian, but it didn't work. Maybe I did something wrong. I cut/pasted your code into the ThisWorkbook Object, and after defining r and x as variants, ran the code. Workbook compiles and the code runs ok, but does not perform the required operation. I still get 3 pages printed, with blank rows. Perhaps I could send you the workbook? It's rather large (550k).

Please let me know what you think.

Thank you for your help,

Darin