VBA filter when search criteria is blank

[Godders199]

Hello, i use the following code, which might be long winded but works, I just need to change the criteria , so if range G17 is blank it just returns the results based on C11 and C20. currently it will return "no Matches found"

Is there any code to do this?

Sheets("cases available ").Select
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
Sheets("instructions ").Select
Dim Usdrws As Long
Usdrws = Sheets("cases available ").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim Startdate As Long, Enddate As Long
Startdate = DateSerial(Year(Date), Month(Date), Day(Date) - 14)
Enddate = DateSerial(Year(Date), Month(Date), Day(Date) + 0)
With Sheets("cases available ").Range("a:ab")
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
.AutoFilter field:=8, Criteria1:=Sheets("instructions ").Range("c11").Value, Operator:=xlFilterValues
.AutoFilter field:=11, Criteria1:="=*" & Sheets("instructions ").Range("c20") & "*", Operator:=xlFilterValues
.AutoFilter field:=30, Criteria1:="=*" & Sheets("instructions ").Range("g17") & "*", Operator:=xlFilterValues
.AutoFilter field:=5, Criteria1:=">=" & Startdate, Operator:=xlFilterValues

End With
If Sheets("cases available ").Range("E1:E" & Usdrws).SpecialCells(xlVisible).Count = 1 Then
MsgBox "No matches re search"
Exit Sub

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Dim wsCasesAvailable As Worksheet, wsInstructions As Worksheet
    Dim Usdrws As Long, Startdate As Long, Enddate As Long


    Set wsCasesAvailable = Worksheets("cases available ")
    Set wsInstructions = Worksheets("instructions ")
    
    
    Startdate = DateSerial(Year(Date), Month(Date), Day(Date) - 14)
    Enddate = DateSerial(Year(Date), Month(Date), Day(Date) + 0)
    
    With wsCasesAvailable
        If .AutoFilterMode Then .AutoFilter.ShowAllData
        Usdrws = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With .Range("a:ab")
            .AutoFilter field:=8, Criteria1:=wsInstructions.Range("C11").Value, Operator:=xlFilterValues
            .AutoFilter field:=11, Criteria1:="=*" & wsInstructions.Range("C20").Value & "*", Operator:=xlFilterValues
            If Len(wsInstructions.Range("G17").Value) > 0 Then .AutoFilter field:=30, Criteria1:="=*" & wsInstructions.Range("G17").Value & "*", Operator:=xlFilterValues
            .AutoFilter field:=5, Criteria1:=">=" & Startdate, Operator:=xlFilterValues
        End With
        If .Range("E1:E" & Usdrws).SpecialCells(xlVisible).Count = 1 Then
            MsgBox "No matches re search", 48, "No Matches"
        Exit Sub
        End If
    End With

Dave

 

[Godders199]

thanks for your help, will give it a go