Do not print empty rows (1 Viewer)

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
Back on the Excel train again.
Is there a relatively simple way to have a bunch of rows formatted, but not print them if they are empty?
Currently if they are empty, but formatted (merged and what not), they will print.

I found a quick simple thing that allowed the user to select a range to hide before printing, but (to me) it is not very intuitive.

Code:
Sub HideBlankRows()
    Dim xRg As Range
    Dim xCell As Range
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim I As Long
   
        On Error Resume Next
        xAddress = Application.ActiveWindow.RangeSelection.Address
        Set xRg = Application.InputBox("Select range to NOT print.", "Print Transmittal", xAddress, , , , , 8)
        Set xRg = Application.Intersect(xRg, ActiveSheet.UsedRange)
        If xRg Is Nothing Then Exit Sub
            If xRg.Areas.Count > 1 Then
                MsgBox "Don't support multiple ranges", , "Kutools for Excel"
                Exit Sub
            End If
           
            xUpdate = Application.ScreenUpdating
            Application.ScreenUpdating = False
           
            For I = 1 To xRg.Rows.Count
                xRg.Rows(I).EntireRow.Hidden = (Application.CountA(xRg.Rows(I)) = 0)
            Next
           
            Application.ScreenUpdating = xUpdate
           
    Call PrintToPDF
   
    On Error Resume Next
        xAddress = Application.ActiveWindow.RangeSelection.Address
    If xRg Is Nothing Then Exit Sub
        xUpdate = Application.ScreenUpdating
        Application.ScreenUpdating = False
        xRg.EntireRow.Hidden = False
        Application.ScreenUpdating = xUpdate
   
   
           
End Sub

Anyone have a better, more user friendly method?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 28, 2001
Messages
27,001
Because of the way Excel's internals work, every cell is an entity unto itself. If you give the printer facility a range of cells and tell it to print, then it is going to print whatever is in that range UNLESS you hide it first. The printer routine within Excel is basically an all-or-nothing-at-all routine. It can't be programmed "on the fly" because we have no "hooks" within that printer driver code to say "print this line but not that one" - at least, we can't tell it that WHILE IT IS PRINTING. Therefore, whatever you are going to do to hide or format something, do it before you give it to the printer.
 

Minty

AWF VIP
Local time
Today, 13:37
Joined
Jul 26, 2013
Messages
10,355
Hide the rows with no data in code, before printing.
As @The_Doc_Man has said the print routines can't do it.
 

Isaac

Lifelong Learner
Local time
Today, 06:37
Joined
Mar 14, 2017
Messages
8,738
If the empty rows are at the beginning or end of the vertical section you wish to print, you could also just use code to determine what rows are blank, and then manipulate the PrintArea accordingly.
 

tmyers

Well-known member
Local time
Today, 09:37
Joined
Sep 8, 2020
Messages
1,090
If the empty rows are at the beginning or end of the vertical section you wish to print, you could also just use code to determine what rows are blank, and then manipulate the PrintArea accordingly.
That is essentially it. The rows would continue vertically until eventually there wont be rows with data. There will be no blanks further up.

I thought about doing a loop checking for data within a specific column from top down then once it found an empty cell within that column, go up one then set that as print area.
 

Isaac

Lifelong Learner
Local time
Today, 06:37
Joined
Mar 14, 2017
Messages
8,738
definitely sounds like you can set the printarea then.
 

Users who are viewing this thread

Top Bottom