If you are a excel vba programmer, you would definitely want to know, that how you can use the Auto filter option using code in macro. As you might be required to provide user with custom filtered data as final output.
We will first have a look at code which will check if the auto filter is already applied in the sheet. If the auto filter already applied then the code will exit (i.e. not execute), else it will apply the filter to the selected or available data within range.
Sub pdCheckAutoFilter()
If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range(“A1”).AutoFilter
Else
End If
End Sub
When executed the above macro will check , if the filters are already applied if yes, then it will exit the code and if not applied then it will filter the available data in current sheet data range.
Now you know how to check and apply the AutoFilter using vba code. Next we will learn how to remove this applied VBA filter.
By just tweaking the above autofilter check and apply code, we can write a code to check and remove Auto Filter
Sub pdCheckRemoveAutoFilter()
If Not ActiveSheet.AutoFilterMode Then
Else
Worksheets(“Sheet1”).AutoFilterMode = False
End If
End Sub
If there is no Autofilter applied to the data, then the above macro will just stop execution. While in case the Autofilter is applied then, the filters will be removed in Sheet1 data.
The above two macros can be used as a sub tasks within a program in order to achieve different outputs as per the requirements.
Thank you for reading. Happy Learning !