CODEWITHSUNDEEP
excelvba
Hery0502
Hery0502

Reputation: 91

Hide rows across multiple sheets

I want to hide rows 16 & 17 across the following tabs in my workbook:

There has to be a better and more efficient way to write this code:

    Sub Macro1()
      Sheet6.Rows("16:17").Hidden = True
      Sheet7.Rows("16:17").Hidden = True
      Sheet8.Rows("16:17").Hidden = True
    End Sub

When this code runs, its take longer than I thought it would.

Any help would be appreciated.

Upvotes: 2

Views: 2191

Answers (3)

VBasic2008
VBasic2008

Reputation: 54777

I Adore ARRAYS - A Working Solution

Hide

Sub HideRows()
  Dim arr As Variant
  Dim i As Integer
  arr = Array(Sheet6, Sheet7, Sheet8)
  For i = LBound(arr) To UBound(arr)
    arr(i).Rows("16:17").Hidden = True
  Next
End Sub

Show All

Sub ShowRows()
  Dim arr As Variant
  Dim i As Integer
  arr = Array(Sheet6, Sheet7, Sheet8)
  For i = LBound(arr) To UBound(arr)
    arr(i).Rows.Hidden = False
  Next
End Sub

Toggle

Sub ToggleRows()
  Dim arr As Variant
  Dim i As Integer
  arr = Array(Sheet6, Sheet7, Sheet8)
  For i = LBound(arr) To UBound(arr)
    arr(i).Rows("16:17").Hidden = Not arr(i).Rows("16:17").Hidden
  Next
End Sub

Thanks to:
ProfoundlyOblivious for profoundly suggesting and providing the 'Toggle' version.
GMalc for providing the idea of yet another way (not ever seen by me) of using an Array.

Upvotes: 1

ProfoundlyOblivious
ProfoundlyOblivious

Reputation: 1485

There are several ways; one that comes to mind is adding them to a dictionary and using For Each to loop through it.

Upvotes: 2

GMalc
GMalc

Reputation: 2628

Use an array of worksheets...

Dim ws As Worksheet
    For Each ws In Worksheets(Array("Sheet6", "Sheet7", "Sheet8"))
        ws.Rows("16:17").Hidden = True
    Next

Upvotes: 1

Related Questions