CODEWITHSUNDEEP
excelvbaautofilter
lukasz93
lukasz93

Reputation: 43

Filter for specific values, but if it doesn't exist it should be clear

I created procedures that always filter for specific criterion in Excel by VBA. My Excel file download data from other database. After downloading, I need to Filter it to get this data. Thus, I created procedures which do it. However, Sometime there is no data, because database was not updated, thus no data was downloaded. In that case, I do not have this criteria in my Filter. Thus, If there is data, Filter should be for "Check", if there is no data, this Filter should be clear. I was wondering about "If" function but I was not aware how to implement it. My code is:

Sub Filter1_Overview()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("X")
    sh.AutoFilterMode = False
    sh.Range("A1:Y7").AutoFilter Field:=7, Criteria1:="Name" 'this line of code is good
    sh.Range("A1:Y7").AutoFilter Field:=18, Criteria1:="Check" 'I would like to modify it
    
End Sub

If I implement this code and there is no data for "Check" criteria, there will be blank cells, but I would like to have only Clear Filter. But, if there is data for "Check", data for "Check" criteria should be shown. Do you have any ideas how to deal with it ? I would appreciate it if someone could instruct myself.

Upvotes: 1

Views: 1127

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

Please, try the next code:

Sub Filter1_Overview()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("X")
    sh.AutoFilterMode = False
    sh.Range("A1:Y7").AutoFilter Field:=7, Criteria1:="Name" 'this line of code is good
    If  WorksheetFunction.CountIf(sh.Range("T:T"), "Check") > 0 then
       sh.Range("A1:Y7").AutoFilter Field:=20, Criteria1:="Check" 
    end if    
End Sub

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54983

Optional Second Filter

Option Explicit

Sub Filter1_Overview()
    With ThisWorkbook.Worksheets("X").Range("A1:Y7")
        .Worksheet.AutoFilterMode = False
        .AutoFilter Field:=7, Criteria1:="Name"
        If Application.CountIf(.Resize(.Rows.Count - 1, 1) _
                .Offset(1, 17), "Check") > 0 Then
            .AutoFilter Field:=18, Criteria1:="Check"
        End If
    End With
End Sub

Upvotes: 1

Related Questions