Clearing table filters via VBA

tomsov

New Member
Joined
Mar 31, 2017
Messages
17
Hello, I'm trying to clear all sorting fields in a table using VBA Worksheet Activate so that when the user accesses a tab, any filters are removed from the columns and a pre-applied filter to two columns.
The vba applied filters are: 'date order' so that newest date is at the bottom of the list, and if field 1 (column A) contains a '1' then only show data with a '1' in the first column (which to us shows that the job is outstanding).

Here's my code, it does work to a certain extent but if a user sets a filter in column 12 (for example) then this filter remains when I really need it to be cleared. The code does set a '1' field and date order as required.[CustomerTAB] is my table reference name.

Private Sub worksheet_Activate()
ActiveWorkbook.Worksheets("Private Customer").ListObjects("CustomerTAB"). _
Sort.SortFields.Clear
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
ActiveWorkbook.Worksheets("Private Customer").ListObjects("CustomerTAB"). _
Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Private Customer").ListObjects("CustomerTAB"). _
Sort.SortFields.Add2 Key:=Range("CustomerTAB[[#All],[Order Date]]"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Private Customer").ListObjects( _
"CustomerTAB").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.ListObjects("CustomerTAB").Range.AutoFilter Field:=1, _
Criteria1:="1"

End Sub


Many thanks
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
Place in the top of your macro: ActiveSheet.ListObjects("CustomerTAB").Range.AutoFilter
 

Watch MrExcel Video

Forum statistics

Threads
1,128,163
Messages
5,629,060
Members
416,363
Latest member
zaveedd

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
Top