Macro to automatically hide rows based on 3 cells

[JAson]

I'm new to vba and have almost no clue how to to work with it, but after
searching several sites, I realize it is most like my best solution. If
anyone can help me that would be great.

The sheet I'm working with is tabbed "Today's Staff" Thought once this works
it will be replicated to reflect each day in the month...that's 37 times. So
this auto hide needs to be workbook associated and not sheet specific.


This sheet pulls alpha code from 4 other tabs (sheets) that are monthly
schedules for different staff groups. This is an IF statement with a HLOOKUP
in it, column AJ. This look up is based on merged cell I3 (a given date)

example: cell AJ7
=IF(HLOOKUP($I$3,'RN-I'!$A$3:$AN$32,AL7,
FALSE)=0,"",HLOOKUP($I$3,'RN-I'!$A$3:$AN$32,AL7, FALSE))

Columns J,K; P,Q; V,W run IF statements that decipher the the letter code
pulled from the monthly schedule into AJ

example: cell J7
=IF($AJ$7="A","â–ˆ",IF($AJ$7="B","â–ˆ",IF($AJ$7="D","â–ˆ",IF($AI$7="M","â–ˆ",IF($AI$7="G","â–ˆ","")))))

Then if there is a "â–ˆ" in columns J,K; P,Q; V,W the the matching column I,
O, & U will display the employee name pulled from the month schedule tabs.
again I used IF statements

example: cell I7
=IF('RN-I'!B5="", "",IF(J7="â–ˆ",'RN-I'!B5,IF(K7="â–ˆ",'RN-I'!B5,"")))

The range of rows that need to be inlcuded is 7 thru 123. It would be nice
if rows 64, 65, 66, 94, 95, & 96 were not included as they are separators
that ID job titles. This is not necessary but would be nice if it could be
accomplished.

Now what I'm after is if the equation for a given row in columns I, O, and U
(all three) results in the display of "", then hide that row of Information.
Otherwise display that row.

Again once I can get this to work, I'm going to replicate it for each day in
a general month. So that all the one needs to do is fill in the 4 month
schedule tabs and then hit print all sheets to have Daily schedule ready for
use.

Thanks for all of the help.
Jason

 

[Bernie Deitrick]

Jason,

This macro will work on the currently selected sheet. I included another one to unhide the rows....

HTH,
Bernie
MS Excel MVP


Sub HideRows()

Dim myR As Long
For myR = 7 To 63
If Range("I" & myR).Value = "" And _
Range("O" & myR).Value = "" And _
Range("U" & myR).Value = "" Then
Rows(myR).Hidden = True
End If
Next myR
For myR = 67 To 93
If Range("I" & myR).Value = "" And _
Range("O" & myR).Value = "" And _
Range("U" & myR).Value = "" Then
Rows(myR).Hidden = True
End If

Next myR
For myR = 97 To 123
If Range("I" & myR).Value = "" And _
Range("O" & myR).Value = "" And _
Range("U" & myR).Value = "" Then
Rows(myR).Hidden = True
End If
Next myR

End Sub

Sub UnhideRows()
Rows.Hidden = False
End Sub

 

[Colbert Zhou [MSFT]]

Hello Jason,

Does Bernie's suggestion resolve your problem? Any future help needed on
this thread?


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).