Hide and Show Rows on Sheet based on similar Array and Filter

[sckuperman]

Greetings, Gurus:

As you may know from my prior questions (and answers), I look and try for days before posting here, but I'm stumped. I'm working on an increasingly complex workbook, heavy on automation and VBA.

On one sheet, I have created two "sections". The first section runs from Row 5 to Row 124 (120 Rows, in total), and can be filtered from Row 4 as the Header, on most columns in those Rows. What I need to do is create a virtual matching filter that will automatically Show or Hide all corresponding Rows in the second "section", from Row 140 to Row 259 (also 120 Rows in total,) when the actual filter is applied in the first section

Because the first section is sometimes filtered as a means to Show / Hide rows, the Rows to Show or Hide may not be contiguous, which I believe requires a Loop to process the logic I want. I may be completely wrong, but my current way of thinking is that I must place the subs for this logic on the specific Worksheet Object as a "Private Sub Worksheet_Change(ByVal Target As Range)" and possibly a corresponding "Private Sub Worksheet_Activate()", with a Global Array on the Sheet Object to store the differential data in between changes. The main issue with this process as I just described is that you cannot declare a Global Array, isn't that right? I also believe that there is no specific Change Event available for applying or re-applying Filter. Am I correct in this?

I cannot just set a dummy cell to COUNTA (for example) and use that as the intermediary reference between cycles since filtering between contiguous cycles may provide the same Row Count, despite "Range("A5:A124").SpecialCells(xlCellTypeVisible) = True" showing different Rows each time.

Please view my current code below and suggest alternatives or edits to show me the correct methodology and help me understand the process to apply it to future similarities!

On the Sheet3 Object, for example:

Public GlobMon() As Variant 'This will not work because you cannot declare a Global Array.  THIS IS THE MAIN ERROR I AM NOW GETTING!

'------------------------------------------------------------------------------------------------------

Private Sub Worksheet_Activate()
j As Variant, rng As Range
Set rng = Range("A5:A124").SpecialCells(xlCellTypeVisible)        'When the Sheet is Activated, the current shown range in the first section is set to rng 
GlobMon = rng                                                            'Then GlobMon (if it worked as a Global Array) is set to rng
    For j = LBound(GlobMon) To UBound(GlobMon)
      Debug.Print GlobMon(j, 1)                                         ' This Loop sets the values of rng into the indices of GlobMon, although could not I instead use GlobMon(j, 1) = rng(j).Value 
    Next j

End Sub

'------------------------------------------------------------------------------------------------------


Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="xxxxxxxxx"
i As Integer, rng1 As Range, LocalMon() As Variant


If Not Application.Intersect(Target, Range(rng)) Is Nothing Then                 'Trigger from a change in cell value in Column A from the first section
Set rng1 = Range("A5:A124").SpecialCells(xlCellTypeVisible)                      'rng1 equals the newly filtered range from the Worksheet Change of the first section, Column A
  LocalMon = rng                                                                    ' Set the quantity of indices for the LocalMon Array to the number of cells shown in the new filter
    For i = LBound(LocalMon) To UBound(LocalMon)
      Debug.Print LocalMon(i, 1)                                                  ' This Loop sets the values of rng into the indices of LocalMon
    Next i


If GlobMon <> LocalMon Then                                                     ' If the filter has changed,
  Call SHTO                                                                              ' Call the Sub, below,
  For j = LBound(LocalMon) To UBound(LocalMon)
      Debug.Print GlobMon(j, 1)                                                     ' And reset the GlobMon for next time
    Next j


End If


ActiveSheet.Protect Password:="1stpass=", UserInterfaceOnly:=True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

'------------------------------------------------------------------------------------------------------


Sub SHTO()
Dim R1 As Integer, R2 As Integer, i As Integer
Rows("140:259").EntireRow.Hidden = False              ' Start by Showing ALL of the Rows in the second section
    For i = 5 To 124                                              ' Iterate a Loop through each of 120 Row comparisons for Boolean Show/Hide
     R1 = i                                                          ' Row of Loop Count representing the Row to compare from the first section
     R2 = i + 135                                                 ' Simple way of incrementing the matching Row to compare with, in the second section
      If Rows(R1).EntireRow.Hidden = True Then        ' If the Row in this the Loop Count from section one is Hidden, then
         Rows(R2).EntireRow.Hidden = True                   ' Hide the matching Row in section two.
      End If
    Next i                                                            ' Loop until done.
End Sub

Please help, or ask me questions, if you believe you can but need more info!

 

[George J]

Well that was fun.
I decided to keep the majority of your code (some commented out) and to stick with using arrays, even though they were driving me demented.

Globmon = rng
If rng was A5:A124 then GlobMon would be a 2 dimensional array.

When creating LocalMon
You could do Localmon = rng to create another 2 dimensional array, but when the filter is applied, you would not be able to use this.
You would need to add each cell separately. So after stepping though the rng and adding each cell to the array, this would be acceptable.
But
This non-coniguous array would be 1 dimensional. Therefore you would not easily be able to compare LocalMon and GlobMon.
That is where I was going in circles.

Solution: Make everything a 1D array and you are then comparing like with like.

I have posted the test data I used below
(disabling and re-enabling events in the SHTO as unhiding the rows caused the calculation event to fire each time)
Hopefully you are able to follow what is happening (I have left some code commented out that I used to step through to check the changes).

Code for the Standard Module:

Option Explicit

Public GlobMon As Variant 'This will not work because you cannot declare a Global Array.  THIS IS THE MAIN ERROR I AM NOW GETTING!


Sub SHTO()
Dim R1 As Integer, R2 As Integer, i As Integer
Application.EnableEvents = False
Range("A140:A259").EntireRow.Hidden = False              ' Start by Showing ALL of the Rows in the second section
    For i = 5 To 124                                              ' Iterate a Loop through each of 120 Row comparisons for Boolean Show/Hide
     R1 = i                                                          ' Row of Loop Count representing the Row to compare from the first section
     R2 = i + 135                                                 ' Simple way of incrementing the matching Row to compare with, in the second section
      If Rows(R1).EntireRow.Hidden = True Then        ' If the Row in this the Loop Count from section one is Hidden, then
         Rows(R2).EntireRow.Hidden = True                   ' Hide the matching Row in section two.
      End If
    Next i                                                            ' Loop until done.
Application.EnableEvents = True
End Sub


Sub test(GM As Variant)
Dim i As Integer
ReDim GlobMon(1 To UBound(GM, 1))
For i = LBound(GM, 1) To UBound(GM, 1)
    GlobMon(i) = GM(i)
Next i
End Sub

Code for the Sheet Events:

Option Explicit

Private Sub Worksheet_Activate()
Dim j As Variant, rng As Range, rng1 As Range, GM As Variant, counter As Integer
    Set rng1 = Range("B5:B124").SpecialCells(xlCellTypeVisible)        'When the Sheet is Activated, the current shown range in the first section is set to rng
'GlobMon = rng                                                            'Then GlobMon (if it worked as a Global Array) is set to rng
    ReDim GM(1 To rng1.Cells.Count)
    For Each rng In rng1
        counter = counter + 1
            GM(counter) = rng
    Next rng


    Call test(GM)
    Debug.Print "GlobMon = " & GlobMon(1) & " set " & GlobMon(120)                                 ' This Loop sets the values of rng into the indices of GlobMon, although could not I instead use GlobMon(j, 1) = rng(j).Value
    Debug.Print "GlobMon = " & GlobMon(1) & " ws_activate " & GlobMon(2)
End Sub


Private Sub Worksheet_Calculate()
'Application.ScreenUpdating = False
'Application.EnableEvents = False
'ActiveSheet.Unprotect Password:="xxxxxxxxx"
Dim i As Integer, rng1 As Range, LocalMon As Variant, rng As Range
Dim j As Integer, GM As Variant, x As Boolean, counter As Integer


'If Target.Column = 1 Then                 'Trigger from a change in cell value in Column A from the first section
    Set rng1 = Range("B5:B124").SpecialCells(xlCellTypeVisible)                      'rng1 equals the newly filtered range from the Worksheet Change of the first section, Column A
    ReDim LocalMon(1 To rng1.Cells.Count)
    For Each rng In rng1
        counter = counter + 1
        LocalMon(counter) = rng
    Next rng
                                                                     ' Set the quantity of indices for the LocalMon Array to the number of cells shown in the new filter
    'For i = LBound(LocalMon) To UBound(LocalMon)
    Debug.Print "GlobMon = " & LBound(GlobMon, 1) & " with " & UBound(GlobMon, 1)                                                   ' This Loop sets the values of rng into the indices of LocalMon
    Debug.Print "GlobMon = " & GlobMon(1) & " ws_calc " & GlobMon(2)
    Debug.Print "LocalMon = " & LBound(LocalMon, 1) & " with " & UBound(LocalMon, 1)
    Debug.Print "LocalMon = " & LocalMon(1) & " ws_calc " & LocalMon(2)
    'Next i
    'i = Empty
    If Not Join(GlobMon, "|") = Join(LocalMon, "|") Then                             ' If the filter has changed,
'                x = True
'                For i = LBound(GlobMon) To UBound(GlobMon)
'                   If GlobMon(i) <> LocalMon(i) Then
'                      x = False
'                      Exit For
'                   End If
'                Next i
'    If x = False Then
        Call SHTO                                                                    ' Call the Sub, below,
    End If
    GM = LocalMon
    Call test(GM)
'End If


'ActiveSheet.Protect Password:="1stpass=", UserInterfaceOnly:=True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Let me know if there are any issues with it or if something is not clear.
George