Suppress Rows While Printing

November 19, 2002 - by Bill Jelen

Jean and Arnoldo asked this week's Excel question.

In Lotus 1-2-3, you could suppress the printing of a row by having the first character be a pipe character (|). This was cool because you could have a formula in column A which could be used to suppress the printing of lines with zero: @IF(E2=0,"|",""). Excel does not seem to offer this feature. I don't want to hide the rows, I just want to suppress their printing in Excel.

MrExcel can empathize with your situation. I used Lotus 1-2-3 for 8 years before my company decided on Excel as the new standard. Switching is never pretty. Excel and Lotus are similar, but the Power Users of Lotus 1-2-3 tend to run into the problems.

Several years after the transition, I now feel Excel is better than Lotus. However, I am acutely aware of the pain required when switching. It is easy to take the changes as a personal attack and be angry with Microsoft. I remember thinking daily, "WHY do I have to put a ',False' as the final argument in all of my @VLOOKUPs, and why doesn't the help file come out and just tell me that?".

If you have macros which were recorded in Lotus 1-2-3, remember that you can use Consulting by MrExcel for macro conversion.

There are some things which Lotus 1-2-3 did easily which are not available in Excel. The beautiful simplicity of /File Combine Add Entire in Lotus is replaced with 8 steps in Excel.

Today's question with the pipe to suppress printing of the rows is another example where Excel offers no similar feature. The LotusPrint macro below will simulate this feature. It is horribly inefficient. The macro examines each cell in the active range of column A. When it finds a pipe, it hides the row. It then prints the worksheet and goes back to unhide the rows which were hidden.

Option Base 1
Sub LotusPrint()
'
' LotusPrint Macro
' This Macro will temporarily hide rows that start with |,
' print the sheet, then unhide those rows. It is meant to
' emulate the Lotus 1-2-3 feature of not printing rows that
' start with a pipe.
' Copyright 1999 www.MrExcel.com
'
'
    Dim UnhideRow() As Single
    Application.ScreenUpdating = False
    ' Locate the final row in column A
    FinalRow = Range("A65536").End(xlUp).Row
    Ctr = 0
' Check and hide each row that starts with a pipe
    For x = 1 To FinalRow
        If Left(Range("A" & x).Value, 1) = "|" Then
            ' Do nothing if the row is already hidden
            If Not Range("A" & x).EntireRow.Hidden Then
                Range("A" & x).EntireRow.Hidden = True
                ' Save the row number so that it can be unhidden
                Ctr = Ctr + 1
                ReDim Preserve UnhideRow(Ctr)
                UnhideRow(Ctr) = x
            End If
        End If
    Next x
    ' Print the sheet
    ActiveWindow.SelectedSheets.PrintOut
    ' Unhide any hidden rows    
    If Ctr > 0 Then
        For x = 1 To Ctr
            Range("A" & UnhideRow(x)).EntireRow.Hidden = False
        Next x
    End If
    Application.ScreenUpdating = True
End Sub