How Do I Quickly Refilter all My Tables with VBA?

I don't like it

I really, really dislike non-tabular datasets and systems that spew out data in a spreadsheet format!

The only way to crunch any meaningful summary out of such dataset is through some extensive formula usage and the subsequent analysis is an incredible pain to maintain on a regular basis.

non-tabular dataset

On top of it, filtering can be another problematic aspect.

Using a table (the so called list object) can alleviate some pain, but it can still be annoying if you’ve got a spreadsheet full of them and you need to make sure that they can be re-filtered consistently without too much user effort.

One way to ensure consistency in filtering is to use a VBA code, which will loop through all tables in the workbook and re-filter them accordingly

Option Explicit
Sub FilterAllTablesInActiveSheet()
Dim Wks As Worksheet
Dim T As ListObject
Dim i As Integer
Dim bU As String
bU = ActiveSheet.Range("C6").Value
For Each Wks In ThisWorkbook.Worksheets
If Wks.Visible = True Then Wks.Activate
If ActiveSheet.ListObjects.Count > 0 Then
For Each T In ActiveSheet.ListObjects
If bU = "All" Then
T.Range.AutoFilter Field:=1
T.Range.AutoFilter Field:=1, Criteria1:=bU
End If
Next T
End If
Next Wks
End Sub

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.