Forum Discussion

chanyuhui :'s avatar
chanyuhui :
Copper Contributor
Apr 26, 2018

Excel - automatically hiding select rows based on drop down selection

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...
Formulas & Functions
Need Help
wmsawchuk's avatar
wmsawchuk
Copper Contributor
Jan 12, 2020

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 

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Jan 12, 2020

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
  • wmsawchuk's avatar
    wmsawchuk
    Copper Contributor
    Jan 12, 2020

    Haytham Amairah 

     

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

    • pmp2020's avatar
      pmp2020
      Copper Contributor
      Feb 05, 2020

      wmsawchuk 

       

      How do you run the macro ?

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Jul 13, 2020
        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)

Resources