VBA - Set Print Area, Based on Visible Rows?

[Cerestes]

I realize excel doesn't (normally) print rows when they're hidden.
Howver, if I have a large range set as the print area, then hide a full page of rows, it wants to print a blank page in the middle of my document.

Is there any snippet of code I can run that dynamically assigns a print area ($A$1:$A$200,$A223:$A274, etc. etc.) based on which rows of the sheet are visible?

 

[Jaafar Tribak]

Do you want to print the hidden rows as blank rows ?

Regards.

 

[Cerestes]

No, I don't want the hidden rows to print at all.

The odd part is if I have a print range of say 1:10, and hide row 6.
It looks correct. You see 1-5,7-10.

If I have a range of pages (say 15 pages) and hide all of the rows that end up on page 6, it prints a blank page 6 with headers, etc.

Basically, I need a macro that goes through a workbook, logs what rows are hidden, and then takes them out of the final print range.

 

[Jaafar Tribak]

See if this works for you .

Place this code in a Bas module and run the Test procedure passing the appropriate worksheet :

Option Explicit


Sub PrintVisibleRows(sht As Worksheet)

    Dim ar() 'will hold hidden rows
    Dim intVisibleAreas, intArea As Integer
    Dim objActiveSheet As Worksheet
    Dim objVisibleCells As Range
    
    '\first get the # of visible rows areas
    intVisibleAreas = sht.Cells.SpecialCells(xlCellTypeVisible).Areas.Count
    
    '\if visible areas >1 it means there are some hidden rows in the worksheet
    If intVisibleAreas > 1 Then
        
        '\remember the current sheet for subsequent retrieval
        Set objActiveSheet = ActiveSheet
        
        '\lots of flickering ahead so we'd better turn off application settings
        With Application
        
            .DisplayAlerts = False
            .ScreenUpdating = False
            
            '\now ,let's make a temporary copy of our sheet so we can mess with it
            '\...we don't want to delete any rows in the original sheet !
            sht.Copy After:=Sheets(Sheets.Count)
            Set objVisibleCells = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)
            
            '\place all the hidden rows in our array and delete them
            '\so as to ensure no blank pages are printed
            With objVisibleCells
            
                ReDim ar(.Areas.Count - 1, 2)
                On Error Resume Next
                
                For intArea = 1 To .Areas.Count - 1
                    ar(intArea, 1) = .Areas(intArea).Row + .Areas(intArea). _
                    Rows.Count: ar(intArea, 2) = .Areas(intArea + 1).Row - 1
                    .Rows(ar(intArea, 1) & ":" & ar(intArea, 2)).Delete
                Next intArea
                
                '\print the sheet minus blank pages
                ActiveSheet.PrintOut
                
                '\we don't need the copy anymore so just delete it
                ActiveSheet.Delete
                
            End With
            
            '\restore normal XL settings
            .DisplayAlerts = True
            .ScreenUpdating = True
            
        End With
        
        '\go back where we were before requesting the printing  -it's only polite :)
        objActiveSheet.Activate
    End If

End Sub


'____________________________________________________________________________________
Sub Test()

    PrintVisibleRows Sheet3 '\replace this parameter with any sheet as required
    
End Sub

Now, if you need to assign this behaviour to the built-in Print Menu, you will need some tweaking.

Regards.