Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to automatically hide rows based on 3 cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to automatically hide rows based on 3 cells
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 "JAson" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to automatically hide rows based on 3 cells
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: . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you hide and unhide rows with a macro based on content of colu | Excel Programming | |||
Macro to hide rows based on criteria | Excel Programming | |||
Macro to Hide rows based on value of column F | Excel Discussion (Misc queries) | |||
Macro to hide rows based on a zero value in a particular cell | Excel Programming | |||
macro to hide rows based on formula? | Excel Programming |