Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional hiding of cells when printing (2003)

    I want to have rows in a worksheet not print if the value in a specific cell in the row is zero or blank. Manually hiding and unhiding the row is a pain, and formatting the row as hidden does not work as it displays and prints a blank row. I want to be able to see the row on the screen, but not have it print and not print a blank row. Just like hidden text in Word. Is there any way to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional hiding of cells when printing (2003)

    You could create a macro that:
    1) hides rows if a specific cell in that row is zero or blank
    2) prints the worksheet
    3) unhides the rows.
    Assign this macro to a keyboard shortcut and/or custom toolbar button. Use this to print the sheet.
    If you need help writing the macro, please provide more details.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional hiding of cells when printing (2003)

    I had wondered about something along those lines, but I'm not a macro writer. What I want is to test all the cells in a column in a range (I want to exclude headers and totals from the test) and if a particular cell is blank or zero, then the row that contains that cell will not print. There could be no, one or several rows that meet the test.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional hiding of cells when printing (2003)

    That is not very specific, so you won't get a specific answer. The following code assumes that the cell to be tested is in column A, and that you want to test rows 4 through 18.

    Sub cmdPrint_Click()
    Dim i As Long
    ' Loop through cells, hide row if necessary
    For i = 4 To 18
    If Range("A" & i) = 0 or Range("A" & i) = "" Then
    Range("A" & i).EntireRow.Hidden = True
    End If
    Next i
    ' Print
    ActiveSheet.PrintOut
    ' Loop through cells to unhide rows
    For i = 4 To 18
    Range("A" & i).EntireRow.Hidden = False
    Next i
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional hiding of cells when printing (2003)

    OK, thanks for that. Rather than naming specific rows or columns, I want to use a named range. So in your example instead of testing column A rows 4 through 18, I would test all the cells in a range named 'Quantity'. How would that be incorporated into the macro?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional hiding of cells when printing (2003)

    Try this variation:

    Sub cmdPrint_Click()
    Dim oCell As Range
    ' Loop through cells, hide row if necessary
    For Each oCell In ActiveSheet.Range("Quantity")
    If oCell = 0 or oCell = "" Then
    oCell.EntireRow.Hidden = True
    End If
    Next oCell
    ' Print
    ActiveSheet.PrintOut
    ' Loop through cells to unhide rows
    For Each oCell In ActiveSheet.Range("Quantity")
    oCell.EntireRow.Hidden = False
    Next oCell
    Set oCell = Nothing
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional hiding of cells when printing (2003)

    Thanks, that worked like a charm. I assigned a toolbar button to the macro and of course it appears in every instance of Excel, regardless of the workbook that's open. Is there a way to have the button appear only when the workbook containing the macro is open?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional hiding of cells when printing (2003)

    You can create the command button in code when the workbook is opened, and delete is when the workbook is closed. But if you want to do it really professionally, it's a lot of work - you would also have to hide the button when the user switches to another workbook and make it visible again when the user switches back. You can also put the toolbar button on a custom toolbar, attach the toolbar to the workbook and delete it each time you close the workbook. See for example <post#=321692>post 321692</post#>.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •