VBA to hide rows

[sammosammo]

Hi all,

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

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:

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!

 

[Fluff]

Will you only ever have Confidential in col A?

 

[sammosammo]

Will you only ever have Confidential in col A?

I can do for sure

 

[Fluff]

Ok how about

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]

Ok how about

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]

Ok how about

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]

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?

 

[sammosammo]

Ignore me, works a treat. Thank you so much !

 

[Fluff]

You're welcome & thanks for the feedback.