excel - Autofilter by criteria, should stop proceeding one of criteria if there is no data in particular column

Question

My aim is to create procedure, which makes always filtering for one criterion (on the column I), but makes next one filtering for criterion (in the column T) provided that it exists.

To start with, I am a beginner, thus thank you for your patience and understanding.

The post is long, because I put screens how to work and how I wish it could work. It helps me out with explaining my problem. I would like to implement my procedure for this simple range of cells:

Thus, we have this range of cells and we need to select a few values by filtering based on criteria:

Sub Filter1()
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.AutoFilterMode = False
    sh.Range("C7:U7").AutoFilter Field:=7, Criteria1:="Lukasz" 
    If WorksheetFunction.CountIf(sh.Range("T:T"), "Check") > 0 Then
       sh.Range("C7:U7").AutoFilter Field:=18, Criteria1:="Check" 
    
    End If
End Sub

It works properly if in the Column I we have value (for example “Lukasz”) and in the column T we have value “Check” as my procedure has been created.

Then, here is the main problem, As you can see for Lukasz in the column I, there are no "Check" values in the column T: Unfortunately, after running my macro it shows empty cells, if we have in the column I data for name “Lukasz”, but there is no data for criterion “Lukasz” in the column T for “Data”, and I run the macro and the result is:

However, in that case, I would like to achieve it:

Therefore, I do not have no idea how to either modify or create my procedure to stop filtering the column T based on criterion, if there is no “Check” values in this column.

Is it possible to either modify my macro or create new one to achieve my goal? If so, could you please modify my procedure and share it in order to achieve my goal? I will test it.

I would appreciate it if you could do it.

question from:https://stackoverflow.com/questions/65952156/autofilter-by-criteria-should-stop-proceeding-one-of-criteria-if-there-is-no-da

Answer 1

The issue is that WorksheetFunction.CountIf(sh.Range("T:T"), "Check") counts all Check in the column T not only the visible ones.

Sub Filter1()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.AutoFilterMode = False
    sh.Range("C7:U7").AutoFilter Field:=7, Criteria1:="Lukasz" 

    Dim FilteredRange As Range  'get only visible cells in column T
    Set FilteredRange = sh.Range("T:T").SpecialCells(xlCellTypeVisible)

    'search if Check can be found (we don't need to count them, one is enough!)
    If Not FilteredRange.Find(What:="Check", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchByte:=True) Is Nothing Then
        sh.Range("C7:U7").AutoFilter Field:=18, Criteria1:="Check"
    End If
End Sub

Note that CountIf does not work with non-continous ranges like FilteredRange returns. But we can use the Range.Find method as we are only interested in the existance of Check and not the amount of Check. The Find returns Nothing if Check was not found.