CODEWITHSUNDEEP
excelvba
C Mig
C Mig

Reputation: 3

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

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.

Upvotes: 0

Views: 535

Answers (1)

Josh Eller
Josh Eller

Reputation: 2065

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

Upvotes: 2

Related Questions