How to stop certain rows from printing if one single cell is blank

0
votes

I am trying to create a macro to print the active sheet. There is always data on page 1,2,3,6 so I have selected "" for the cell and have a cell selected that will never have data in it so they will always print. For pages 4 & 5 I only want them to print if there is something in one specific cell defined and if the cell is empty it will not print that page(s). Page 4 only has one cell I need to check if it is blank but on page 5 I need to check 4 cells and if any of them are not blank I need page 5 to print. The first 3 pages print fine right now but then I get an error when I get to page 4

I've tried changing the if statement for page 4 so that it will look at the single cell and then if it meets the rule it will hide the cells (I would like them to then unhide after printing is done of page 6 or just not print those rows instead of hiding and then unhiding).

Public Sub PrintReport()

Dim strSheetName As String strSheetName = ActiveSheet.Name

If Range("A2").Value = "" Then 'Page 1

Range("A1:R91").PrintOut

If Range("A93").Value = "" Then 'Page 2

Range("A92:R157").PrintOut

If Range("A158").Value = "" Then 'Page 3

Range("A158:R199").PrintOut

If Range("C202").Value = "" Then 'Page 4

Range("A200:A243").EntireRow.Hidden = True

Else

Range("A200:A243").EntireRow.Hidden = False

If Range("C246").Value And Range("A269").Value And Range("E285").Value And Range("E293").Value = "" Then 'Page 5

Range("A244:A301").EntireRow.Hidden = True

Else

Range("A244:A301").EntireRow.Hidden = False

If Range("P302").Value = "" Then ' Page 6

Range("A320:R325").PrintOut

End If

End If

End If

End If

End If

End If

End Sub

I would like it to print out each page that meets the specified criteria and then prints or does not print those specified cells. Pages 1-3 print fine as they will always print, but it always errors out on page 4 where the range is noted as Range("A200:A243").Hidden = True. I need this to either hide and then unhide those rows after printing the remaining pages or just not print it and not unhide.

1
excelvba
I strongly encourage you to adopt more standard indentation methods. This will go a long way in helping you to trouble shoot your code - urdearboy
Excel VBA help is your friend: "The specified range must span an entire column or row." You can use Range("A200:A243").entirerow.Hidden =(Range("C202").Value = "") and avoid the If statement. - SJR
Echo @urdearboy here. See how Rubberduck's indenter can help with fixing that ;-) (disclaimer: I'm one of the admins of this OSS project) - Mathieu Guindon
This code doesn't compile - you've got an extra End If in there. - Comintern
@SJR - But what if I do want to print that page will this still print? Also how could I get it to unhide those rows after page 6 is printed so when I look at the document they are still visible? - C Mig

1 Answers

2
votes

You can only hide rows or columns, not just any range. Add .EntireRow to all your hide/unhide statements to have the hide/unhide work on the entire row, like so:

Range("A244:A301").EntireRow.Hidden = True