Excel - automatically hiding select rows based on drop down selection

%3CLINGO-SUB%20id%3D%22lingo-sub-187276%22%20slang%3D%22en-US%22%3EExcel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187276%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%20class%3D%22m_1018432915032829176quote%22%3EHi%20there!%20I%20have%20a%20drop%20down%20selection%20of%2010%20items%20on%20my%20dashboard%20tab%20and%20am%20trying%20to%20only%20show%20rows%2057%20to%2072%20when%20I%20select%20one%20of%20them.%20I%20am%20using%20the%20following%20code%20but%20somehow%20it%20is%20not%20working%20and%20my%20dashboard%20does%20not%20change%20at%20all.%20However%2C%20when%20I%20key%20in%20something%20into%20a%20random%20cell%2C%20the%20page%20reloads%20and%20rows%2057%3A72%20become%20hidden.%20But%20then%2C%20it%20stays%20the%20same%20across%20all%20my%20selections%20and%20I%20cannot%20unhide%20it%20again%20through%20the%20drop%20box%20selection.%20May%20I%20know%20if%20anyone%20has%20an%20alternative%20suggestion%3F%20Thank%20you%20in%20advance!%3C%2FDIV%3E%3CDIV%20class%3D%22m_1018432915032829176quote%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22m_1018432915032829176quote%22%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FDIV%3E%3CDIV%20class%3D%22m_1018432915032829176quote%22%3EIf%20Target.Column%20%3D%202%20And%20Target.Row%20%3D%203%20And%20Target.Value%20%3D%20%221%22%3C%2FDIV%3E%3CDIV%20class%3D%22m_1018432915032829176quote%22%3EThen%20Application.Rows(%2257%3A72%22).Select%20Application.Selection.EntireRow.Hidden%20%3D%20False%3C%2FDIV%3E%3CDIV%20class%3D%22m_1018432915032829176quote%22%3EElse%20Application.Rows(%2257%3A72%22).Select%20Application.Selection.EntireRow.Hidden%20%3D%20True%3C%2FDIV%3E%3CDIV%20class%3D%22m_1018432915032829176quote%22%3EEnd%20If%3C%2FDIV%3E%3CDIV%20class%3D%22m_1018432915032829176quote%22%3EEnd%20Sub%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-187276%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187432%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187432%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20provide%20me%20with%20screenshots%20of%20your%20dashboard%20to%20figure%20out%20what%20the%20problem%20is.%3C%2FP%3E%3CP%3EIt's%20best%20if%20you%20can%20attach%20the%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187416%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187416%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Haytham%2C%20unfortunately%20it%20seems%20like%20it%20is%20my%20excel%20sheet%20that%20is%20causing%20the%20problem.%20The%20same%20issue%20remains%20(that%20the%20bars%20don't%20get%20hidden%20until%20I%20type%20something%20into%20cell%20J3%2C%20after%20which%20it%20does%20not%20unhide%20again).%20I%20tried%20your%20method%20on%20a%20new%20excel%20file%20and%20it%20worked.%20If%20you%20happen%20to%20know%20how%20I%20can%20troubleshoot%20this%20issue%20please%20let%20me%20know.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-187321%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-187321%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understand%20you%20well%2C%20you%20want%20to%20show%20rows%2057%20to%2072%20when%20you%20select%20the%20value%20of%201%20from%20the%20dropdown%20list%2C%20otherwise%2C%20you%20want%20to%20hide%20them.%3CBR%20%2F%3EI%20think%20that%20the%20dropdown%20list%20in%20cell%20B3%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBased%20on%20that%2C%20please%20try%20this%20code%20instead%3A%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20Range(%22B3%22)%20%3D%20%221%22%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Rows(%2257%3A72%22).EntireRow.Hidden%20%3D%20False%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Rows(%2257%3A72%22).EntireRow.Hidden%20%3D%20True%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1103333%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1103333%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20info%20on%20how%20to%20use%20a%20macro%20to%20hide%20rows.%26nbsp%3B%20I%20have%20a%20question%20as%20an%20extension%20to%20the%20first%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see%20how%20you%20hide%20rows%20based%20on%20the%20content%20of%20a%20cell.%26nbsp%3B%20Is%20it%20also%20possible%20to%20use%20the%20same%20macro%20to%20set%20the%20print%20area%20of%20the%20document%20so%20that%20it%20prints%20the%20rows%20that%20are%20still%20active%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3Ewmsawchuk%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1103709%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1103709%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F514936%22%20target%3D%22_blank%22%3E%40wmsawchuk%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20that's%20possible!%3C%2FP%3E%3CP%3ETry%20this%20code%3A%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3E%20%20If%20Range(%22B3%22)%20%3D%20%221%22%20Then%3CBR%20%2F%3E%20%20%20%20Rows(%2210%3A15%22).EntireRow.Hidden%20%3D%20False%3CBR%20%2F%3E%20%20%20%20ActiveSheet.PageSetup.PrintArea%20%3D%20%22%24A%241%3A%24H%2415%22%3CBR%20%2F%3E%20%20Else%3CBR%20%2F%3E%20%20%20%20Rows(%2210%3A15%22).EntireRow.Hidden%20%3D%20True%3CBR%20%2F%3E%20%20%20%20ActiveSheet.PageSetup.PrintArea%20%3D%20%22%24A%241%3A%24H%249%22%3CBR%20%2F%3E%20End%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1103830%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1103830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20the%20code%20to%20set%20the%20print%20area.%26nbsp%3B%20It%20works%20great%20and%20I%20really%20appreciate%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1154050%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1154050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F514936%22%20target%3D%22_blank%22%3E%40wmsawchuk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20you%20run%20the%20macro%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1519524%22%20slang%3D%22de-DE%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1519524%22%20slang%3D%22de-DE%22%3EMaybe%20the%20other%20way%20round...%20%3CBR%20%2F%3E%20ALT%20%2B%20F11%20and%20click%20on%20the%20desired%20picture%2C%20copy%20the%20code%20...%20change%20the%20lines%20as%20desired%20beforehand.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Private%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%20%3CBR%20%2F%3E%20Dim%20Range%20As%20Range%2C%20Calculation%20As%20Long%20%3CBR%20%2F%3E%20Calculation%20%3D%20Application.Calculation%20%3CBR%20%2F%3E%20Application.Calculation%20%3D%20xlCalculationAutomatic%20%3CBR%20%2F%3E%20Application.ScreenUpdating%20%3D%20False%20%3CBR%20%2F%3E%20Me.Unprotect%20(%221234%22)%20%3CBR%20%2F%3E%20Set%20Range%20%3D%20Intersect(Target%2C%20Range(%22A15%3AA1009%22))%20%3CBR%20%2F%3E%20If%20Not%20Area%20Is%20Nothing%20Then%20%3CBR%20%2F%3E%20Application.EnableEvents%20%3D%20False%20%3CBR%20%2F%3E%20Range(%22A15%3AA1009%22).%20EntireRow.Hidden%20%3D%20False%20%3CBR%20%2F%3E%20Range(%22A10%3AA1000%22).%20EntireRow.AutoFit%20%3CBR%20%2F%3E%20Range(%22A%22%20%26amp%3B%2045%20%2B%20WorksheetFunction.Counta(%20_%20%3CBR%20%2F%3E%20Range(%22A15%3AA1009%22))%20%26amp%3B%20%22%3AA1009%22).%20EntireRow.Hidden%20%3D%20True%20%3CBR%20%2F%3E%20End%20If%20%3CBR%20%2F%3E%20Me.Protect%20(%221234%22)%20%3CBR%20%2F%3E%20Application.EnableEvents%20%3D%20True%20%3CBR%20%2F%3E%20Application.Calculation%20%3D%20Calculation%20%3CBR%20%2F%3E%20Application.ScreenUpdating%20%3D%20True%20%3CBR%20%2F%3E%20End%20Sub%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Regards%2C%20%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20(Knows%20I%20don't%20know%20anything)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1519528%22%20slang%3D%22de-DE%22%3ERe%3A%20Excel%20-%20automatically%20hiding%20select%20rows%20based%20on%20drop%20down%20selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1519528%22%20slang%3D%22de-DE%22%3Edesired%20picture%20%3D%20disired%20sheet%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
Hi there! I have a drop down selection of 10 items on my dashboard tab and am trying to only show rows 57 to 72 when I select one of them. I am using the following code but somehow it is not working and my dashboard does not change at all. However, when I key in something into a random cell, the page reloads and rows 57:72 become hidden. But then, it stays the same across all my selections and I cannot unhide it again through the drop box selection. May I know if anyone has an alternative suggestion? Thank you in advance!
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row = 3 And Target.Value = "1"
Then Application.Rows("57:72").Select Application.Selection.EntireRow.Hidden = False
Else Application.Rows("57:72").Select Application.Selection.EntireRow.Hidden = True
End If
End Sub
9 Replies
Highlighted

Hi,

 

If I understand you well, you want to show rows 57 to 72 when you select the value of 1 from the dropdown list, otherwise, you want to hide them.
I think that the dropdown list in cell B3?

 

Based on that, please try this code instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B3") = "1" Then
        Rows("57:72").EntireRow.Hidden = False
    Else
        Rows("57:72").EntireRow.Hidden = True
    End If
End Sub
Highlighted

Thanks Haytham, unfortunately it seems like it is my excel sheet that is causing the problem. The same issue remains (that the bars don't get hidden until I type something into cell J3, after which it does not unhide again). I tried your method on a new excel file and it worked. If you happen to know how I can troubleshoot this issue please let me know. Thank you!

Highlighted

Hi,

 

Please provide me with screenshots of your dashboard to figure out what the problem is.

It's best if you can attach the workbook.

Highlighted

@Haytham Amairah 

Thank you for the info on how to use a macro to hide rows.  I have a question as an extension to the first question.

 

I see how you hide rows based on the content of a cell.  Is it also possible to use the same macro to set the print area of the document so that it prints the rows that are still active?

 

Thanks,

wmsawchuk 

Highlighted

@wmsawchuk

 

Yes, that's possible!

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3") = "1" Then
Rows("10:15").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$15"
Else
Rows("10:15").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$9"
End If
End Sub
Highlighted

@Haytham Amairah 

 

Thank you very much for the code to set the print area.  It works great and I really appreciate your help.

Highlighted
Highlighted
maybe the other way round...
ALT + F11 and click on the desired picture, copy the code ... change the lines as desired beforehand.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Bereich As Range, Berechnung As Long
Berechnung = Application.Calculation
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Me.Unprotect ("1234")
Set Bereich = Intersect(Target, Range("A15:A1009"))
If Not Bereich Is Nothing Then
Application.EnableEvents = False
Range("A15:A1009").EntireRow.Hidden = False
Range("A10:A1000").EntireRow.AutoFit
Range("A" & 45 + WorksheetFunction.CountA( _
Range("A15:A1009")) & ":A1009").EntireRow.Hidden = True
End If
Me.Protect ("1234")
Application.EnableEvents = True
Application.Calculation = Berechnung
Application.ScreenUpdating = True
End Sub

Regards,
Nikolino
(Knows I don't know anything)
Highlighted
desired picture = disired sheet