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]

I am not sure I followed all that, but when using a filter, rather than the counta function, can you use subtotal to do what you need?

 

[sckuperman]

I am not sure I followed all that, but when using a filter, rather than the counta function, can you use subtotal to do what you need?

I know I wrote a lot - but it is only so other's answers can be as specific as possible. I don't follow your suggestion about the use of the SubTotal function. How/where/why would you apply it?

Thanks,

 

[George J]

I have read through the code and still not sure I understand what you are trying to do overall.

I would set up a subtotal formula, so that when something is filtered, this changes.
IF the code is only to run when something is filtered/unfiltered, put the subtotal on another worksheet and use a worksheet_Calculate event. When the subtotal changes, the calculate event kicks in and runs your macro.

 

[sckuperman]

I have read through the code and still not sure I understand what you are trying to do overall.

I would set up a subtotal formula, so that when something is filtered, this changes.
IF the code is only to run when something is filtered/unfiltered, put the subtotal on another worksheet and use a worksheet_Calculate event. When the subtotal changes, the calculate event kicks in and runs your macro.

In my initial explanation, I wrote,

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.

Whether I count cells/rows that contain text in ranges that show or hide, or I count those with numerals makes no difference. SUBTOTAL is as equally ineffective as COUNTA, COUNT, COUNTIF, or any similar function because the output is unreliable as a trigger. For example, if the initial state of filtering the worksheet shows four rows, and a user alters the filter to show four different rows, the output of SUBTOTAL and related functions is going to be four; which is the same both times, and will not trigger the event. Similarly, if I enumerate all of the rows to be filtered, in a hidden dummy column, (which I have already done in Column "A"), and SUBTOTAL those enumerations that are shown, the output can still equal the value SUBTOTALed from a a different filtered set of Rows. For example, initially filtering the enumerated Rows #5, #10, and #20 to show, yields #45 in SUBTOTAL. However, so does changing the filter to showing only enumerated Rows #1 (Row 5) and #44 (Row 49).

Therefore, as simply as I can explain while being explicit as possible, I need a more reliable means of: 1. Recognizing a new filtered state of Shown Rows (triggered via difference) in the Range of Rows from 5 to 124, 2. Then Showing/Hiding associated rows offset by 135 Rows, in the Range of Rows from 140 to 259.

Regardless of my current code, can you suggest the best way to do this?

 

[George J]

Sorry, but the subtotal with worksheet_calculation was my best attempt at this.
The only other thing i can think of is creating a userform to do the filtering from.

I created a test workbook with 4 rows of data

a	b	c
1	1	1
1	1	1
2	2	2

<tbody>
</tbody>

I applied the data filter to these cells only.
Below this I put the formula: =SUBTOTAL(9,C2:C4)

I added a worksheet_calculate event and put int = 1 with a breakbreakpoint here to check if it fired.
When I filtered c to show 1's it fired as it changed from 4 to 2
when I filtered c to show 2's it fired - even though the value hadn't changed.

Is the worksheet_calculate event not firing for you?

I'm calling it a night, but hopefully others will pitch in here. As I said though, you may need to create a userform to do the filtering if this is not working for you.

 

[George J]

Couldn't leave this be.

I'm too tired to think straight, but it seems that when the filter occurs, the subtotal should be enough to trigger the worksheet calculate event.
If you are wanting this to get the Global address of GlobMon set, then try this.

In a standard module, at the top put:
Global GlobMon as string 'just had GlobMon as range for testing and the address

test(GM)
GlobMon = GM
end sub

You can set GlobMon in worksheet_activate - not sure if you would also need to change it in the worksheet_calculate event so it is ready for the next change, after you have called SHTO.
GM = rng.address
call test(GM) 'this passes the GM value to the test macro where the GlobMon value can be set as a Global Variable = to GM

'possibly add after the call SHTO and If statement
GM = localMon.address
call test(GM)

'this will reset GM to whatever LocalMon has been filtered to, so if another filter is done, it should run SHTO again (unless this isn't required).

Hope this helps.

 

[George J]

Were you able to get this working?

 

[sckuperman]

Hi, George:

There are two issues to consider. The second is the trigger, but the first is whether or not the code works manually.

I have been testing SHTO by setting a filter, running the macro manually, and viewing the results. Here are the results:

A. Start from scratch. I set a filtered set of rows in section one to show Rows 8, 17, 22, 37, and 44. Run SHTO. The matching rows in section two (offset from section one by 135 rows per Row) are correctly hidden/shown.

B. I change the filter by adding another element in the filter to show Row 67. Run SHTO. Row 67 Shows. Row 202 DOES NOT Show. Subsequent calls to SHTO have no additional effect.

C. I remove Row 67 from the filter, keeping 8 ,17 ,22 ,37 , and 44 in place. Row 67 Hides. Run SHTO. No change in section two. Subsequent calls to SHTO have no additional effect.

D. I reset the filter. I then apply a filter to select Rows 9, 13, 23, and 27 to Show. Run SHTO. ZERO Rows in section two Show. Run SHTO again, immediately and the correct Rows in section two Show.

E. I repeat #B and #C with the same results.

Can you explain what is happening? How does the Filter function in the GUI really work?

Do I need to capture Filter settings in an array and reset the filter / reapply filters with user changes in each call to SHTO??? Any ideas on how to code this??

Thanks,

-Scott

 

[George J]

Unfortunately as I am using an old version of Excel at home; I have been tying myself in knots all night with arrays and variants.
I would probably pass the GlobMon and LocalMon data to a string to show the addresses - but that's probably because I'm miffed at the variants not working on my machine like I think they should.

Anyhoo, SHTO is not working as you say.
Instead of Rows("140:259").EntireRow.Hidden = False
Possibly change this to Range("A140:A259").EntireRow.Hidden = False
This should make the rows visible after you code hides them first time. It never made the cells visible again which is why the additional changes were not displayed.
Not quite sure what is happening with the last part of D to show the correct rows.
Also step through the code with F8 to check if you need to disable events when SHTO makes a change to the worksheet.

I'll pop back tomorrow - possibly with some old-school code that you can tidy up.
Hopefully it will allow you to filter rows 5 to 124 and the subsequent changes are copied in rows 140 to 259.