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

lukasz93

New Member
Joined
Jan 29, 2021
Messages
3
Platform
  1. Windows
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:
1.jpg



Thus, we have this range of cells and we need to select 2 values by filtering based on criteria, it is my procedure:

VBA Code:
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.
2.jpg

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:
3.jpg

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:
4.jpg


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





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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,615
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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:
VBA Code:
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,058
Office Version
  1. 2016
Platform
  1. Windows
sh.Range("C7:U7").AutoFilter Field:=7, Criteria1:="Lukasz" ==> sh.Range("A7:U7").AutoFilter Field:=7, Criteria1:="Lukasz"
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,615
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

What about
VBA Code:
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,058
Office Version
  1. 2016
Platform
  1. Windows
Maybe the old filter is still active: clear first all filters
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,456
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

lukasz93

New Member
Joined
Jan 29, 2021
Messages
3
Platform
  1. Windows
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:
VBA 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,456
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,404
Messages
5,624,543
Members
416,034
Latest member
Shiv kumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top