excel - vba button click to hide and unhide several rows - Stack Overflow

I have this macro, connected to a button to hide and unhide a row.

Sub Cloud_Click()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

If ws.Shapes("Cloud 2").TextFrame2.TextRange.Text = "openen" Then
    ws.Shapes("Cloud 2").TextFrame2.TextRange.Text = "verbergen"
    ws.Rows("5").Hidden = False
Else
    ws.Shapes("Cloud 2").TextFrame2.TextRange.Text = "openen"
    ws.Rows("5").Hidden = True
End If

End Sub

And this is for row 5, but i want this to work for several rows, so it could be row: 5 and then 10, 15, 20, 22, 26 and so on and so on.

But i can't seem to figure out how to add multiple rows to the procedure.

please help :)

i tried to add multiple line number but i get an error each time

I have this macro, connected to a button to hide and unhide a row.

Sub Cloud_Click()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

If ws.Shapes("Cloud 2").TextFrame2.TextRange.Text = "openen" Then
    ws.Shapes("Cloud 2").TextFrame2.TextRange.Text = "verbergen"
    ws.Rows("5").Hidden = False
Else
    ws.Shapes("Cloud 2").TextFrame2.TextRange.Text = "openen"
    ws.Rows("5").Hidden = True
End If

End Sub

And this is for row 5, but i want this to work for several rows, so it could be row: 5 and then 10, 15, 20, 22, 26 and so on and so on.

But i can't seem to figure out how to add multiple rows to the procedure.

please help :)

i tried to add multiple line number but i get an error each time

• excel
• vba
• button

Share Improve this question Follow asked Jan 2 at 14:47 Jeffrey van MalssenJeffrey van Malssen 1111 silver badge11 bronze badge 6

• 1 Do you mean hide row 5 and row 10 and row 15 etc ? How is 5, 10, 15 etc determined ? Range("5:5,10:10,15:15").EntireRow.Hidden = True will hide those 3 rows. – CDP1802 Commented Jan 2 at 15:02
• Is it fixed number of rows or the currently selected row(s)? – Siddharth Rout Commented Jan 2 at 15:07
• well to be exact, as it is looking now the numbers are fixed, so its row 5, 8, 11, 14, 17! – Jeffrey van Malssen Commented Jan 2 at 15:09
• Try this ws.Range("5:5,8:8,11:11,14:14,17:17").EntireRow.Hidden = True – Siddharth Rout Commented Jan 2 at 15:14
• Dim r: For Each r In Array(5, 8, 11, 14, 17): ws.Rows(r).Hidden = False: Next – CDP1802 Commented Jan 2 at 15:17

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 2

Several ways to do so. The optimal solution may depend on how you identify the rows you want to show/hide - you just listed some rows as examples.

Dim r As Range
Set r = ws.Range("5:5,10:10,15:15,20:20,22:22,26:26").EntireRow
If ws.Shapes("Rectangle 1").TextFrame2.TextRange.Text = "openen" Then
    ws.Shapes("Rectangle 1").TextFrame2.TextRange.Text = "verbergen"
    r.Hidden = False
Else
    ws.Shapes("Rectangle 1").TextFrame2.TextRange.Text = "openen"
    r.Hidden = True
End If

Or use an array and loop over it:

Dim rowList As Variant, i As Long
rowList = Array(5, 10, 15, 20, 22, 26)

Dim show As Boolean
show = ws.Shapes("Rectangle 1").TextFrame2.TextRange.Text = "openen"
ws.Shapes("Rectangle 1").TextFrame2.TextRange.Text = IIf(show, "verbergen", "openen")

For i = LBound(rowList) To UBound(rowList)
    ws.Rows(rowList(i)).Hidden = Not show
Next i

If you have a lot of rows to show/hide, it may be faster to use Union first. Setting the hidden property is a rather slow action and reducing it to only one call can be faster.

rowList = Array(5, 10, 15, 20, 22, 26)
Dim r As Range
For i = LBound(rowList) To UBound(rowList)
    If r Is Nothing Then
        Set r = ws.Rows(rowList(i))
    Else
        Set r = Union(r, ws.Rows(rowList(i)))
    End If
Next i
r.EntireRow.Hidden = Not show

If you just want to show/hide every 5th row, a simple for-loop could do the trick (again, you could consider to use the logic of using a Union to speed things up).

Dim row As Long
For row = 5 to 100 Step 5
    ws.Rows(row).Hidden = Not show
Next row

Or maybe the rows you want to show/hide have a special marker. The following code assumes that those rows have an "X" if column A:

Dim markers As Variant, row As Long
markers = ws.Range("A1:A100").Value

For row = 1 To UBound(markers, 1)
    If markers(row, 1) = "X" Then
        ws.Rows(row).Hidden = Not show
    End If
Next