VBA anomaly when filtering table from Worksheet_Calculate

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
When the user filters Table1, my objective is to have Table2 on the same sheet filtered so that both tables have the same ID items visible.

Since VBA doesn't have an event that is triggered when a table is filtered, I'm using the Worksheet_Calculate event which is triggered due to the presence a SUBTOTAL formula that counts visible rows in Table1.

The problem is that VBA doesn't seem to be able to AutoFilter Table2 if Worksheet_Calculate is triggered as a result of filtering Table1.

Here's a simplified example to illustrate the problem...


Excel 2016 (Windows) 32 bit
ABCDEF
1Table1
2IDNameMeasure
3#111Name1277Test4
4#222Name2155Formula:5
5#333Name3296
6#444Name4109
7Subtotal:837
8
9Table 2
10IDNameMeasure
11#111Name1637
12#222Name2476
13#333Name3454
14#444Name4127
15Subtotal:1694
Sheet1
Cell Formulas
RangeFormula
F4=F3+1
C7=SUBTOTAL(9,Table1[Measure])
C15=SUBTOTAL(9,Table2[Measure])


Note: My finished code will read the visible items' IDs in Table1 into an array. I've removed that step for this example to demonstrate that isn't causing the anomaly.

Code:
'--worksheet code in Sheet1
Private Sub Worksheet_Calculate()
 Application.EnableEvents = False
 SyncFiltersByID
 Application.EnableEvents = True
End Sub

Code:
'--code in Module1
Sub SyncFiltersByID()
 Dim vVisibleItemList As Variant
 
 '--simplified for this example- will read Table1 in final code
 vVisibleItemList = Array("#111", "#333", "#444")

 '--sync Table2 to list of IDs in Table1
 With Sheet1.ListObjects("Table2").Range
   .AutoFilter Field:=1, _
      Criteria1:=Array(vVisibleItemList), Operator:=xlFilterValues
 End With

End Sub

If I enter any value in cell B21, Worksheet_Calculate is triggered and the code successfully filters Table2 to show ID's 111,222, and 444.

If I filter Table1, Worksheet_Calculate is triggered and the code unexpectedly applies the filters intended for Table2 to the ID field of Table1!

If I Cut and Paste Table2 into Sheet2 (and modify the code to reference it on Sheet2), when Table1 is filtered, the code successfully filters Table2 to show ID's 111,222, and 444.

The code will also work if I eliminate the Worksheet_Calculate and require the user to push a button to call SyncFiltersByID.

The best workaround I've found is to add a Slicer to the ID field of Table2, and sync the tables through the Slicer. I'll go with that unless someone can suggest a better solution. Note that the Tables do not use the same data source, so they can't be sync'd by slicers alone.

TIA
 
Last edited:
Here's a tested workaround for you (cloak and mirrors approach)

Logic
- put Table2 on a different sheet
- worksheet_calculate no longer displays the behaviour described :)
- amend worksheet_calculate so that it syncs Table2 (in other sheet) before COPYING Table2 below Table1
- the copy looks like a table but is in fact a standard range
- if required the range could be converted to a table

To test
- put Table2 on another sheet and amend the references accordingly

Code:
'in Table1's sheet module
Private Sub Worksheet_Calculate()
    Dim r As Long
    Application.EnableEvents = False
[I][COLOR=#006400]'delete previous copy of Table2[/COLOR][/I]
    r = Me.ListObjects(1).Range.Offset(Me.ListObjects(1).Range.Rows.Count + 1).Row
    Me.Rows(r).Resize(1000).EntireRow.Delete 'deletes previous copy
[COLOR=#006400][I]'sync[/I][/COLOR]
    SyncFiltersByID
[COLOR=#006400]'copy Table2[/COLOR]
    [COLOR=#ff0000]Sheet99[/COLOR].ListObjects(1).Range.SpecialCells(xlCellTypeVisible).Copy Me.Range("A" & Rows.Count).End(xlUp).Offset(2)
    Application.EnableEvents = True
End Sub

Code:
'in Module1 (as before)
Sub SyncFiltersByID()
  Dim vVisibleItemList As Variant
   '--simplified for this example- will read Table1 in final code
  vVisibleItemList = Array("#111", "#333", "#444")
   '--sync Table2 to list of IDs in Table1
  With [COLOR=#ff0000]Sheet99[/COLOR].ListObjects(1).Range
     .AutoFilter Field:=1, Criteria1:=Array(vVisibleItemList), Operator:=xlFilterValues
  End With
End Sub
 
Last edited:
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Yongle, Thanks for the idea.

That would accomplish the task, however I think that's more complicated than my current workaround of filtering Table2 using a slicer.
 
Upvote 0
After a lot of testing, it appears that running sync inside worksheet_calculate is the issue
- its a bug and the problem happens because there are 2 or more tables on the same sheet (it shouldn't but it does!)
- I have tried various things and (no matter what I try) that appears to be the stumbling block
- the macro runs correctly if it is run in any other way

Here is a simple alternative (which you may have already dismissed ;))

Delete worksheet_calculate and use this instead
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
       SyncFiltersByID
    Application.EnableEvents = True
End Sub

After filtering in Table1 simply click anywhere in in the sheet to sync
(or you could restrict it to clicking anywhere inside Table1 etc)
 
Last edited:
Upvote 0
Yes, you are correct that I chose against using the SelectionChange event. I don't want to rely on, or impose upon the user that they select the worksheet in order to have the filters sync.

The workaround I'm using with the slicer is fine. I just didn't want that to be overkill if there was a missing some simple way to get autofilters to work.

Thanks again to you and Fluff for confirming that you experience the same unexpected behavior when running the OP code.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top