How can I trigger a filter when a specific text is in a column?

Question

I want to create an Excel macro using Finance data out of our ERP system.

Not all options in the "status" column are there every time I dump the data. The "status" column consists of things like: 'approved', 'rejected', 'awaiting approval', etc.

I want to first check whether 'rejected' is in the column before filtering (then I have different code triggering giving those cells a colour).

Because 'rejected' isn't always a status, the code stops and the macro doesn't finish.

ActiveSheet.Range("$A$1:$K$128").AutoFilter Field:=10, Criteria1:= _
  "Rejected"
Range("H125").Select
Range(Selection, Selection.End(xlDown)).Select
'...(more code)

How can I let the filter only trigger when "rejected" is present in that column (column K)?

Hard to give best advice without seeing rest of code but have you looked into `On Error Resume Next`? — findwindow, Mar 30 '22 at 15:19
Use Match() on the filter column to check for at least one row in the data. — Tim Williams, Mar 30 '22 at 16:07

score 0 · Answer 1 · answered Mar 30 '22 at 16:17

To check if a range of data contains a value, you can use WorksheetFunction.CountIf. I assume that your status is in column "J" (as you set your filter on column 10). I don't know why your hard code the range to 128 rows, I would suggest to do this dynamic:

With ActiveSheet   
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
    
    Dim dataRange As Range
    Set dataRange = .Range(.Cells(1, "A"), .Cells(lastRow, "K"))
End With

Dim countRejected As Long
countRejected = WorksheetFunction.CountIf(dataRange.Columns(10), "Rejected")
If countRejected > 0 Then
    ' Filter out Rejected data
    dataRange.AutoFilter Field:=10, Criteria1:="Rejected"
    ' Do your logic when you have some Rejected rows
Else
    ' No Filter needed
    ' Do your logic when you have no Rejected rows
End If

As you don't show the rest of your code: It for sure needs cleanup - you shouldn't use Select. Read (and understand) How to avoid using Select in Excel VBA

How can I trigger a filter when a specific text is in a column?

1 Answers1