VBA to hide rows

sammosammo

New Member
Joined
Sep 16, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm using toggle buttons to hide rows in this Excel:
1600265432320.png


The current code I have is so that when Togglebutton1 is pressed, "confidential" rows are hidden and patient details remain and the other way round when Togglebutton2 is pressed:
VBA Code:
Private Sub ToggleButton1_Click()
     
    If ToggleButton1.Value = True Then
         'This area contains the things you want to happen
         'when the toggle button is not depressed
        Rows(3).EntireRow.Hidden = True
        Rows(5).EntireRow.Hidden = True
        Rows(7).EntireRow.Hidden = True
 
         
    Else
         'This area contains the things you want to happen
         'when the toggle button is depressed
        Rows(3).EntireRow.Hidden = False
        Rows(5).EntireRow.Hidden = False
        Rows(7).EntireRow.Hidden = False

        

    End If
End Sub


Private Sub ToggleButton2_Click()

    If ToggleButton2.Value = True Then
         'This area contains the things you want to happen
         'when the toggle button is not depressed
        Rows(2).EntireRow.Hidden = True
        Rows(4).EntireRow.Hidden = True
        Rows(6).EntireRow.Hidden = True
        
            Else
         'This area contains the things you want to happen
         'when the toggle button is depressed
        Rows(2).EntireRow.Hidden = False
        Rows(4).EntireRow.Hidden = False
        Rows(6).EntireRow.Hidden = False

End If

End Sub

I'm just wondering how to going forward how to write the code for Togglebutton1 so that it knows that anything with "Confidential" in column A should be hidden rather than me listing out each row in code. The full data set is going to be quite complicated since rows and their respective confidential rows below them will move up and down the sheet. Also some "Patients" will have more than one "Confidential" row so it won't be alternate rows that need to be hidden.

Any help would be greatly appreciated!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Will you only ever have Confidential in col A?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
Ok how about
VBA Code:
Private Sub ToggleButton1_Click()
   With Range("A2:A" & Rows.Count).SpecialCells(xlConstants).EntireRow
      .Hidden = Not .Hidden
   End With
End Sub

Private Sub ToggleButton2_Click()
   With Range("A2:A" & Rows.Count).SpecialCells(xlBlanks).EntireRow
      .Hidden = Not .Hidden
   End With
End Sub
This will hide the rows based on whether col A is blank or not.
 

sammosammo

New Member
Joined
Sep 16, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok how about
VBA Code:
Private Sub ToggleButton1_Click()
   With Range("A2:A" & Rows.Count).SpecialCells(xlConstants).EntireRow
      .Hidden = Not .Hidden
   End With
End Sub

Private Sub ToggleButton2_Click()
   With Range("A2:A" & Rows.Count).SpecialCells(xlBlanks).EntireRow
      .Hidden = Not .Hidden
   End With
End Sub
This will hide the rows based on whether col A is blank or not.

Thank you! I'll give it a go
 

sammosammo

New Member
Joined
Sep 16, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Ok how about
VBA Code:
Private Sub ToggleButton1_Click()
   With Range("A2:A" & Rows.Count).SpecialCells(xlConstants).EntireRow
      .Hidden = Not .Hidden
   End With
End Sub

Private Sub ToggleButton2_Click()
   With Range("A2:A" & Rows.Count).SpecialCells(xlBlanks).EntireRow
      .Hidden = Not .Hidden
   End With
End Sub
This will hide the rows based on whether col A is blank or not.


Toggle button 2 worked perfectly, thank you! It hid all the rows with col A blank. But I can't seem to get togglebutton1 to work. Am I right in reading it as, if togglebutton1 is clicked, rows that have col A not blank will be hidden?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Am I right in reading it as, if togglebutton1 is clicked, rows that have col A not blank will be hidden?
That's right. In what way isn't it working?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,254
Messages
5,576,977
Members
412,755
Latest member
vintage88
Top