Macro or VBA to Hide rows if a blank cell

Posted by L.E. Patton on December 16, 2001 2:09 AM

Created a workbook(template) that has 15 worksheets, 9 of the worksheets(for each filler machine at a milk plant) have a list of production for a day, what I would like is to hide the rows of product that is not being run that day. I key the production wanted on one sheet use vlookup to pull to the correct sheets named f1 to f9 and then print them for the filler operators.
vlookup is in col H. Col. G contains product no#, would need it to stop hiding rows where there is not a product no#

Posted by Tom Dickinson on December 16, 2001 11:13 AM

Try this:

Sub RowDisplayToggle()
Dim Cnt1, Cnt2 As Integer
For Cnt1 = 1 To 9
Cnt2 = 1
Do While Range("F" & Cnt1 & "!G" & Cnt2) &LT;&GT; Empty
If Range("F" & Cnt1 & "!H" & Cnt2) > 0 And Range("F" & Cnt1 & "!H" & Cnt2).EntireRow.Hidden Then
Range("F" & Cnt1 & "!H" & Cnt2).EntireRow.Hidden = False
Else
If Range("F" & Cnt1 & "!H" & Cnt2) = 0 And Not (Range("F" & Cnt1 & "!H" & Cnt2).EntireRow.Hidden) Then
Range("F" & Cnt1 & "!H" & Cnt2).EntireRow.Hidden = True
End If
End If
Cnt2 = Cnt2 + 1
Loop
Next
End Sub

If you have too many items, you may want to turn off the screen refresh at the start of the program. Also, if you need to look at the sheets on the screen, be aware that the display may not be at the top of each sheet.

Got Milk?
Tom D.

Posted by L.E. Patton on December 16, 2001 7:24 PM

Thanks Tom, I will try this
117 across 9 sheets

:you may want to turn off the screen refresh
How?

Posted by Tom Dickinson on December 17, 2001 10:08 PM

Start the macro with the line
Application.ScreenUpdating = False

I believe they have made it so that updates become active once a macro is finished, but you may want to set it to TRUE anyway as the last line of the macro.