VBA - Hiding group of rows

[Kmartin10]

Hello all,

Very new to VBA so bear with me - the attached picture is what I have so far and does hide rows 40 -42. I would like to hide multiple groups of rows with the same button and have tried the following with no success:

xAddress = "40:42, 44:46, 48:50"

xAddress = "40:42", "44:46" , "48:50"

xAddress = "40:42" AND "44:46" AND "48:50"

Any help is appreciated.

 

[GWteB]

How about ...

Sub Kmartin10()
    
    Dim xAddress As Variant, xRows As Range, rw As Variant

    xAddress = Array("40:42", "44:46", "48:50")
    For Each rw In xAddress
        If xRows Is Nothing Then
            Set xRows = ActiveSheet.Rows(rw)
        Else
            Set xRows = Application.Union(xRows, ActiveSheet.Rows(rw))
        End If
    Next
    xRows.EntireRow.Hidden = True   '/ False
End Sub

 

[My Aswer Is This]

Try this:
The script toggles them from hidden to not hidden.

Sub Hide_Rows()
'Modified  9/30/2021  3:52:27 PM  EDT

    Rows("40:42").Hidden = Not Rows("40:42").Hidden
    Rows("44:46").Hidden = Not Rows("44:46").Hidden
    Rows("48:50").Hidden = Not Rows("48:50").Hidden
End Sub

 

[Kmartin10]

How about ...

Sub Kmartin10()
   
    Dim xAddress As Variant, xRows As Range, rw As Variant

    xAddress = Array("40:42", "44:46", "48:50")
    For Each rw In xAddress
        If xRows Is Nothing Then
            Set xRows = ActiveSheet.Rows(rw)
        Else
            Set xRows = Application.Union(xRows, ActiveSheet.Rows(rw))
        End If
    Next
    xRows.EntireRow.Hidden = True   '/ False
End Sub

GWteB: This worked great. If I wanted to use the same button to Unhide those same rows would I input "xRows.EntireRow.Show = False" before "End Sub?

Thanks

 

[GWteB]

If I wanted to use the same button to Unhide those same rows would I input "xRows.EntireRow.Show = False" before "End Sub?

Yes, but slightly more neat would be to separate the code in procedures, like:

Sub Kmartin10()

    Dim xAddress As Variant

    xAddress = Array("40:42", "44:46", "48:50")
    ToggleSomeRows xAddress
End Sub


Sub ToggleSomeRows(ByVal argAddressArray As Variant)
    
    Dim xRows As Range, rw As Variant
    For Each rw In argAddressArray
        If xRows Is Nothing Then
            Set xRows = ActiveSheet.Rows(rw)
        Else
            Set xRows = Application.Union(xRows, ActiveSheet.Rows(rw))
        End If
    Next
    xRows.EntireRow.Hidden = Not xRows.EntireRow.Hidden
End Sub

 

[My Aswer Is This]

Yes, but slightly more neat would be to separate the code in procedures, like:

Sub Kmartin10()

    Dim xAddress As Variant

    xAddress = Array("40:42", "44:46", "48:50")
    ToggleSomeRows xAddress
End Sub


Sub ToggleSomeRows(ByVal argAddressArray As Variant)
   
    Dim xRows As Range, rw As Variant
    For Each rw In argAddressArray
        If xRows Is Nothing Then
            Set xRows = ActiveSheet.Rows(rw)
        Else
            Set xRows = Application.Union(xRows, ActiveSheet.Rows(rw))
        End If
    Next
    xRows.EntireRow.Hidden = Not xRows.EntireRow.Hidden
End Sub

Just asking. Why do we need to use all this code to just hide a few rows.
My script does the job with very little code.

 

[GWteB]

Personal preference. First of all, the array with row names is easy to expand without having to write a new line of code for each (group of) rows. Second, toggle between hidden and visible from multiple not adjacent ranges is much faster if you do it in one go, rather than each separate range.

 

[Kmartin10]

Yes, but slightly more neat would be to separate the code in procedures, like:

Sub Kmartin10()

    Dim xAddress As Variant

    xAddress = Array("40:42", "44:46", "48:50")
    ToggleSomeRows xAddress
End Sub


Sub ToggleSomeRows(ByVal argAddressArray As Variant)
   
    Dim xRows As Range, rw As Variant
    For Each rw In argAddressArray
        If xRows Is Nothing Then
            Set xRows = ActiveSheet.Rows(rw)
        Else
            Set xRows = Application.Union(xRows, ActiveSheet.Rows(rw))
        End If
    Next
    xRows.EntireRow.Hidden = Not xRows.EntireRow.Hidden
End Sub

GWteB - This is great work! I would like to take this sheet a step further and hide a group of rows based on the value of a specific cell. For instance: If B1 = 0 Then hide rows 2:4 , If B5 = 0 Then hide rows 6:8 , If B9 = 0 Then hide rows 10:12 ...... This edit isn't necessary for the functionality of the sheet but would clean up unnecessary rows!

Again, thanks for your expertise and time!

 

[GWteB]

You are welcome and thanks for letting us know.

I would like to take this sheet a step further and hide a group of rows based on the value of a specific cell. For instance: If B1 = 0 Then hide rows 2:4 , If B5 = 0 Then hide rows 6:8

In that case it would probably be more convenient to take advantage of the worksheet change event handler of that particular sheet if the contents of those cells are changed manually, or the worksheet calculation event handler if those cells contain a formula. For a brief introduction of events, take a look over here ...

Events in Excel VBA

Events are actions performed by users which trigger Excel VBA to execute code.

www.excel-easy.com