Excel VBA Macro does Autofilter by criteria, but Macro should stop proceeding one of criteria if there is no data in particular column.

[lukasz93]

Dear All,

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 would like to add that I am a beginner in this and it is difficult for me to implement any ideas. Thank you for your understanding.
I uploaded a few screenshots in order to show you 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 2 values by filtering based on criteria, it is my procedure:

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.

 

[maabadi]

Welcome to MrExcel Message Board.
You have Check at header and then Your Data Filtered Also Based Column T.
You have 2 Way
1. Change Header From Check to another word
OR
2. Change Code to this:

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") > 1 Then

sh.Range("C7:U7").AutoFilter Field:=18, Criteria1:="Check"

End If

End Sub

 

[mart37]

sh.Range("C7:U7").AutoFilter Field:=7, Criteria1:="Lukasz" ==> sh.Range("A7:U7").AutoFilter Field:=7, Criteria1:="Lukasz"

 

[lukasz93]

Hi @maabadi Unfortunately it does not work, I have the same result.
@mart37 it doesn't work properly.

 

[maabadi]

What about

Sub Filter1()
Dim sh As Worksheet, Lr as Long
Set sh = ThisWorkbook.Sheets("Sheet1")
Lr = Sh.Cells(Rows.Count, 18).End(xlup).Row
sh.AutoFilterMode = False
sh.Range("C7:U7").AutoFilter Field:=7, Criteria1:="Lukasz"
If WorksheetFunction.CountIf(sh.Range("T8:T" & Lr), "Check") > 0 Then
sh.Range("C7:U7").AutoFilter Field:=18, Criteria1:="Check"
End If
End Sub

If doesn't Work
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

 

[mart37]

Maybe the old filter is still active: clear first all filters

 

[Fluff]

How about

If WorksheetFunction.CountIfs(sh.Range("I:I"), "Lukasz", sh.Range("T:T"), "Check") > 0 Then

 

[lukasz93]

Hi All.
Thank you for your support.
To solve this problem, I needed to change the name of the cell to avoid having the same name of the cell as my headers.
Instead of "Check", I put "Check In" For example.
Then, it works.
I used this code:

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

 

[Fluff]

Glad you sorted it & thanks for the feedback.