Toggle for Hiding or Unhiding a Few Specific or Named Rows

[daveG777]

I have very simple VBA that hides or unhides rows that are already highlighted.

However, I need a toggle, so that the same code will hide or unhide specific rows. Also, I'd prefer to hide or unhide a named range of rows, if that's possible, rather than just give the usual column letters and row numbers.

Here's as far as I got with using simple memorized keystrokes:
_____________________________________________

Sub rowsHide()​

​

'Unprotect sheet​

ActiveSheet.Unprotect​

​

'Hide rows that are highlighted​

Selection.EntireRow.Hidden = True​

​

End Sub
_____________________________________________​

Sub rowsUnhide()​

​

'Unprotect sheet​

ActiveSheet.Unprotect​

​

'Unhide rows that are highlighted​

Selection.EntireRow.Hidden = False​

​

End Sub​

_____________________________________________

Here's what someone else did, but I don't understand or need the complications with the button, or about the stuff on a different sheet.

VBA Toggle Button - Hide/Unhide rows on different sheet​

By onthegreen03​

Hi - I set up a toggle button (ActiveX Controls) that hides/unhides columns, code is attached below. How would I change the code if I wanted to hide/unhide the same columns but on a different sheet? I want to keep the toggle button on the active sheet (let's call that "Sheet 1") and when pressed will hide/unhide columns on "Sheet 2". Thanks for your help!​

​

Private Sub ToggleButton1_Click()​

Dim xAddress As String
xAddress = "C:N"
If ToggleButton1.Value Then
Application.ActiveSheet.Columns(xAddress).Hidden = True
ToggleButton1.Caption = "Show Months"
Else
Application.ActiveSheet.Columns(xAddress).Hidden = False
ToggleButton1.Caption = "Hide Months"
End If
End Sub​

​

_____________________________________________

​

By the way, I'm really grateful when people write comments to explain their syntax or logic.

Thanks,
Dave

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
'Modified  5/3/2022  11:51:26 PM  EDT
Range("Alpha").EntireRow.Hidden = Not Range("Alpha").EntireRow.Hidden
End Sub

 

[davidruben]

To simplly toggle the selected rows:

Sub hiderows()
    Selection.EntireRow.Hidden = Not (Selection.EntireRow.Hidden)
End Sub

Selection is whatever cells already selected. ".EntireRow" then takes the whole of thoise rows (ratehr than selective columns that might have been selected within the rows). We can read the .Hidden attribute, use NOT to invert that and then set as the current hidden status

To state a range of lets say rows 5 to 7, rather than what has already been selected:

Sub hiderows()
    Rows("5:7").Hidden = Not (Rows("5:7").Hidden)
End Sub

This is awkward coding, especially as defining "Rows("5:7") twice. Using "With ... End With" we can specify the object of interest:

Sub hiderows()
    With Rows("5:7")
        .Hidden = Not (.Hidden)
    End With
End Sub

To define the required rows with variables:

Sub hiderows()
Dim RowStart As Integer, RowEnd As Integer
    RowStart = 5
    RowEnd = 7
    With Rows(RowStart & ":" & RowEnd)
        .Hidden = Not (.Hidden)
    End With
End Sub

Lastly if already have a named area, say "NamedArea", then:

Sub hiderows()
    With Range("NamedArea").EntireRow
            .Hidden = Not (.Hidden)
    End With
End Sub

 

[daveG777]

You said

Lastly if already have a named area, say "NamedArea", then:​

​

VBA Code:​

Sub hiderows()
With Range("NamedArea").EntireRow
.Hidden = Not (.Hidden)
End With
End Sub​

Can we make it toggle for a named area? (Maybe I'm missing something above.)

Otherwise, to use the above as a separate macro for unhiding the rows, would I replace line 3 with

.Hidden = (.Hidden)​

​

or​

​

.Hidden = Yes (.Hidden)​

Thanks!

 

[davidruben]

Above coding toggles ("Nots" the existing value), so just re-run teh code to revert back.
The .Hidden parameter takes values of "True" or "False" (non-zero number or zero), so as separate specific routines, rather than a toggle:

Sub hider()
    Range("NamedArea").EntireRow.Hidden = True
End Sub

Sub unhider()
    Range("NamedArea").EntireRow.Hidden = False
End Sub

 

[My Aswer Is This]

If the range is on another sheet use this:

Private Sub CommandButton1_Click()
'Modified  5/4/2022  12:09:53 AM  EDT
Sheets("Alpha").Range("Cake").EntireColumn.Hidden = Not Sheets("Alpha").Range("Cake").EntireColumn.Hidden
End Sub

 

[My Aswer Is This]

Both of my codes toggle named ranges.
Have you tried them?

 

[daveG777]

Both of my codes toggle named ranges.
Have you tried them?

I'm excited to see if it works when I get back to my computer tomorrow.​

​

Thanks so much!​

 

[daveG777]

Above coding toggles ("Nots" the existing value), so just re-run teh code to revert back.
The .Hidden parameter takes values of "True" or "False" (non-zero number or zero), so as separate specific routines, rather than a toggle:

Sub hider()
    Range("NamedArea").EntireRow.Hidden = True
End Sub

Sub unhider()
    Range("NamedArea").EntireRow.Hidden = False
End Su
[QUOTE="davidruben, post: 5878463, member: 60337"]
Above coding toggles ("Nots" the existing value), so just re-run teh code to revert back.
The .Hidden parameter takes values of "True" or "False" (non-zero number or zero), so as separate specific routines, rather than a toggle: 
 
[CODE=vba]Sub hider()
    Range("NamedArea").EntireRow.Hidden = True
End Sub

Sub unhider()
    Range("NamedArea").EntireRow.Hidden = False
End Sub

[/QUOTE]

I was surprised that the "above code" that I had originally complained about did everything I wanted, acting just like a toggle. However, I'm going to play with, and learn from, everyone's comments over the next few days, and then I'll offer my two cents.

What I DON'T know about Excel VBA, and even formulas, is so much more than I DO know. It's also SO LITTLE when compared to some of the mavens in this discussion group.

Thanks,
Dave

 

[My Aswer Is This]

I was surprised that the "above code" that I had originally complained about did everything I wanted, acting just like a toggle. However, I'm going to play with, and learn from, everyone's comments over the next few days, and then I'll offer my two cents.

What I DON'T know about Excel VBA, and even formulas, is so much more than I DO know. It's also SO LITTLE when compared to some of the mavens in this discussion group.

Thanks,
Dave
[/QUOTE]
Not sure what your saying here. My script Toggles a named range like I thought you asked for. But it's true with Excel there are 20 ways to do everything. So I gave you what i use.