Excel VBA – Remove the autofilter in Excel VBA

Auto filter is a very useful comment to extract the data which satisfy the required criteria. When we program for other users, it is good to remove the applied autofilter to the range to eliminate the confusion.

Function Copyfilter(Sht As Worksheet, Rng As Range, FField As Integer, Crit As Variant)

Dim Rngfilter As Range

Sht.Select

With Sht

Rng.AutoFilter Field:=FField, Criteria1:=Crit

'----The following comments can test whether the auto filter or filter mode is active.

If .FilterMode = True Then MsgBox "Filter is used" Else MsgBox "Filter is not used"

If .AutoFilterMode = True Then MsgBox "Filter is visible" Else MsgBox "Filter is not visible"

'-----The following can remove the autofilter

If .FilterMode = True Then .ShowAllData     

End With

End Function

However, using the .ShowAllData sometimes gives the error message. For example, there are no data meeting the criteria.

The error message comes.

One way to solve the problem is to add one comment before .ShowAllData.

On Error Resume Next

If .FilterMode = True Then .ShowAllData

The error message would not show and the code can continue execute.  However, data in the filtered range still hide.

The better way to do it is to use the following comment.

Rng.AutoFilter

It removes the previous autofilter applications in the select range.

Another case is that we want to detect the presence of any active autofilter and remove them.

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.Range("A1").AutoFilter

This comment sometimes gives the error. It is better to combine with error handling code

On Error Resume Next

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.Range("A1").AutoFilter

If Err.Number <> 0 Then MsgBox "Checking the Filter in the sheets."

© Copyright Exceltipsandkeys All Rights Reserved.