Am I using too many arrays?

DDRA Steampunk

New Member
Joined
Feb 10, 2017
Messages
14
Solved right after initial post (by experimenting). :p

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.

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,670
Office Version
365
Platform
Windows
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:
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,044
Messages
5,466,209
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top