Reputation: 27
I've got the below code that is used to hide/unhide rows. In my spreadsheet I have a whole lot of images which are small down arrows. I've assigned the below code to those down arrows, effectively to use them to minimise and maximise rows as the spreadsheet has hundreds and hundreds of rows of data.
If I press on one down arrow it hides rows under it, then press again it unhides it, however if i press one down arrow and then another arrow the second click doesn't do anything I then have to press on it one more time to get it to work, or I have to press the original down arrow again. It's obviously due to cell A1 containing x. Is there any way to fix this?
Sub Advertising_20_21()
If Range("A1") = "x" Then
ActiveSheet.Range("A1").Value = ""
ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden = True
Else
ActiveSheet.Range("A1") = "x"
ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden = False
End If
End Sub
Upvotes: 0
Views: 79
Reputation: 2875
Test if the row is hidden directly rather than the value of A1 (which I assume is shared by all your arrow procedures and is therefore, the source of your undesired behaviour)
Sub Advertising_20_21()
If Not ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden Then
ActiveSheet.Range("A1").Value = ""
ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden = True
Else
ActiveSheet.Range("A1") = "x"
ActiveSheet.Range("Advertising_20_21").EntireRow.Hidden = False
End If
End Sub
The following is a slightly better approach
Sub ToggleHiddenRow(rng As Range)
With rng.EntireRow
.Hidden = Not .Hidden
End With
End Sub
Sub ToggleAdvertising_20_21()
ToggleHiddenRow ActiveSheet.Range("Advertising_20_21")
End With
Upvotes: 2