Am I using too many arrays?

[DDRA Steampunk]

Solved right after initial post (by experimenting).

I need to autohide a crazy huge number of rows in one sheet, it currently has 9 sub tables (see Sample 2), and may need 11. Page not included because it's about 10 paper sheets long. Trying to get all arrays on one toggle button.

The new end product code is below.

Public Sub ToggleButton1_Click()
   Dim BeginRow As Variant
   Dim EndRow As Variant
   Dim ChkCol As Variant
   Dim RowCnt As Long
   Dim aryCnt As Long


   BeginRow = Array("3", "115", "227", "339", "451", "563", "675", "787", "899")
   EndRow = Array("110", "222", "334", "446", "558", "670", "782", "894", "1006")
   ChkCol = Array("3", "3", "3", "3", "3", "3", "3", "3", "3")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
   If ToggleButton1.Value = False Then
      For aryCnt = 0 To 8
         ActiveSheet.Rows(BeginRow(aryCnt) & ":" & EndRow(aryCnt)).Hidden = False
      Next aryCnt
   Else
      With ActiveSheet
         For aryCnt = 0 To 8
            For RowCnt = BeginRow(aryCnt) To EndRow(aryCnt)
               If .Cells(RowCnt, CInt(ChkCol(aryCnt))).Value < 1 Then
                  .Cells(RowCnt, CInt(ChkCol(aryCnt))).EntireRow.Hidden = True
               Else
                  .Cells(RowCnt, CInt(ChkCol(aryCnt))).EntireRow.Hidden = False
               End If
            Next RowCnt
         Next aryCnt
         .UsedRange.EntireColumn.AutoFit
      End With
   End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[JackDanIce]

I'd argue you are using too many arrays. Not fully tested but I think you can use just one array with fewer inner loops, and suspect there is further optimisation to below:

Public Sub ToggleButton1_Click()

    Dim x As Long
    Dim RowX(1 To 3)    As Long: RowX(1) = 3: RowX(2) = 108: RowX(3) = 4
    
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    With ActiveSheet
        If .AutoFilter Then .AutoFilterMode = False
        For x = 1 To 9
            If Not ToggleButton1.Value Then
                .Cells(RowX(1).Resize(RowX(2)), 1).EntireRow.Hidden = ToggleButton1.Value
            Else
                If Application.CountIf(.Cells(RowX(1).Resize(RowX(2)), "<" & 1)) Then
                    With .Cells(RowX(1).Resize(Row(2)))
                        .EntireRow.Hidden = False
                        .AutoFilter , field:=1, Criteria1:="<" & 1
                        .SpecialCells(xlCellTypeVisible).EntireRow.Hidden = True
                    End With
                End If
            End If
            RowX(1) = .Cells(RowX(1), 1).Offset(RowX(2) + RowX(3)).Row
        Next x
    End With
    
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    Erase RowX
    
End Sub

Not tested so you may need to adjust it, but your arrays show the range sizes are constant and the blank rows inbetween are constant 4 so (bit like algebra?) you can avoid explicitly stating the row numbers.

Also, why are you using string values (e.g. "3", "115") when row numbers are.. numeric data types? It would be more intuitive if your array of numbers were numbers and not wrapped in speech marks