Friday, September 15, 2017

VBA code to check filter status in worksheet!!!

Filter is used to check specific type of data in excel workbooks. As we know excel is capable of handling thousand of records with various kind of datatype. So, it obvious to have data in text, dates, percentage and number format. These format are mainly know as datatype. We often use filter to see particular type of data in excel worksheet. While processing data using macro it is very important to check whether data is filtered on not. 

Sometimes, our data has some filtering criteria on excel worksheet which is not needed while running the macro for specific automation task. Therefore, it is necessary to remove filter on the data before running the macro. In this topic, we will see how we can check filter status in excel worksheet.

Here is the VBA code to check filter status in worksheet: 

Option Explicit

Sub Check_Autofilter_Status()
' Author: Dreams24
' Written for VBA Tricks and tips blog

'Declare Variable
Dim wbk as Workbook
Dim sht As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Variable used to assign worksheet name

'Variable set for activeworkbook
Set wbk = Activeworkbook 

If wbk.Sheets(sht).FilterMode = False Then

    'Put your code when filter is disabled
    MsgBox "Filter is not enabled", vbInformation

    'Put your code when filter is enabled
    MsgBox "Filter is enabled", vbInformation
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

No comments: