Forum Discussion

AB_2019's avatar
AB_2019
Copper Contributor
Jul 12, 2019

Hide and Show Rows in a Sheet Based on a cell value

Hi All,   Hoping someone can help me out.   I would like to hide rows on a sheet based on a number in a cell (A6).   Starting with row 9 first, so if number 1 is in the A6 cell then row 9 is sh...
excel
Macros and VBA
Berndvbatanker's avatar
Berndvbatanker
Iron Contributor
Jul 12, 2019

AB_2019 

Hi,

you can use the following event, see attached example, too.

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngArea As Range

If Target.Address <> "$A$6" Then Exit Sub
Rows.Hidden = False
Select Case Target.Value
Case 1
Set rngArea = Range(Rows(10), Rows(Rows.Count))
rngArea.Hidden = True

Case 2
Set rngArea = Range(Rows(11), Rows(Rows.Count))
rngArea.Hidden = True

Case 3
Set rngArea = Range(Rows(12), Rows(Rows.Count))
rngArea.Hidden = True

Case 4
Set rngArea = Range(Rows(13), Rows(Rows.Count))
rngArea.Hidden = True

Case 5
Set rngArea = Range(Rows(14), Rows(Rows.Count))
rngArea.Hidden = True

Case 6
Set rngArea = Range(Rows(15), Rows(Rows.Count))
rngArea.Hidden = True

Case 7
Set rngArea = Range(Rows(16), Rows(Rows.Count))
rngArea.Hidden = True

Case 8
Set rngArea = Range(Rows(17), Rows(Rows.Count))
rngArea.Hidden = True

Case Else
Set rngArea = Range(Rows(8), Rows(Rows.Count))
rngArea.Hidden = False
End Select

End Sub

 

Best regards

Bernd

https://vba-tanker.com/

AB_2019's avatar
AB_2019
Copper Contributor
Jul 12, 2019

Berndvbatanker 

Hi Bernd,

 

Thanks for your reply,

 

I am not sure if I am using this correctly, I have downloaded your example and have put some different numbers in the highlighted A6 cell but it does not have any effect on the sheet, all rows remain visible.

 

Can you please let me know if i am missing something.

  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    Jul 12, 2019

    Hi,
    macros are activated?

    Copy the macro to a new workbook behind first worksheet and test again

    • AB_2019's avatar
      AB_2019
      Copper Contributor
      Jul 12, 2019

      Berndvbatanker 

      Hi, Yes they are on. I have seen it work in a new spreadsheet.

       to 

      I would like to try a different approach to this one. 

       

      Can you please let me know if it is possible to create a macro to apply to an active X toggle button to show and hide rows.

       

      The values are "Yes" to show the row and "No" to hide the row.

       

      These are in column A rows 9 to 258.

       

      Thank you.

      • Berndvbatanker's avatar
        Berndvbatanker
        Iron Contributor
        Jul 12, 2019

        AB_2019 

        Hi, that's a good idea. Look at the attachment.

         

        Private Sub ToggleButton1_Click()
        Dim rngArea As Range

        Set rngArea = Range(Rows(9), Rows(258))
        If Me.ToggleButton1.Value = True Then
        rngArea.Hidden = False
        Me.ToggleButton1.Caption = "Hide"
        Else
        rngArea.Hidden = True
        Me.ToggleButton1.Caption = "Show"
        End If

        End Sub

         

        Best regards

        Bernd

        My website https://vba-tanker.com/

Resources