VBA - Excel - Code returning blank sheet after applying filter - condition Criteria is not found

Question

I have written a VBA code to autofilter on 2 Criteria, and it is working perfectly. The only issue is when if the 2nd citeria is not, it returns a blank sheet, and then I have to clear filters to get my data back. I want to modify code so it will just process my message box and return my sheet untouched.

Sub filter_Available()

Dim strInput As String
Dim fltrdrng As Range
Dim lUpper As Long
Dim LstRow2 As Long
Dim j As Integer
Dim r As Range

Sheets("Available").Select
Set r = Range(Range("D1"), Range("D1").End(xlDown))

    strInput = InputBox("Enter The Project Code")
    
    
    ActiveSheet.Range("A:D").AutoFilter Field:=1, Criteria1:=strInput
    ActiveSheet.Range("A:D").AutoFilter Field:=2, Criteria1:=Sheets("Requirement").Range("G2").Value
    
    j = WorksheetFunction.Count(r.Cells.SpecialCells(xlCellTypeVisible))
    If j = 0 Then
    
        MsgBox "The ICD was not found"
        Exit Sub
        
        Else: If j > 0 Then Set fltrdrng = Intersect(ActiveSheet.UsedRange, ActiveSheet.UsedRange.Offset(1)).SpecialCells(xlCellTypeVisible)
        lUpper = UBound(Split(fltrdrng.Address, "$"))
    
        LstRow2 = Split(fltrdrng.Address, "$")(lUpper)
        Range("A2:D" & LstRow2).Copy (Sheets("TempAvail").Range("A1"))
        
     End If
    
    
End Sub

Answer 1

You must clear autofilter at the end of your function.

Also, you musn't count the first cell for WorksheetFunction.Count(), isn't it?

Sub filter_Available()
    Dim criterialValue1 As String
    Dim criterialCell2 As Range
    Dim fltrdrng As Range
    Dim lUpper As Long
    Dim LstRow2 As Long
    Dim j As Integer
    Dim r As Range
    Dim r2 As Range
    Dim firstCellofLastCol As Range
    Dim selectedRange As Range
    Dim availableSheetName As String
    Dim firtColName As String
    Dim lastColName As String
    Dim destSheet As Object
    Dim activeSheet As Object
    
    ' Params
    availableSheetName = "Available"
    firtColName = "A"
    lastColName = "D"
    Set criterialCell2 = Sheets("Requirement").Range("G2")
    Set destSheet = Sheets("TempAvail")

    destSheet.Cells.Clear
    
    Set activeSheet = Sheets(availableSheetName)
    Set firstCellofLastCol = activeSheet.Range(lastColName + "1")
    Set r = activeSheet.Range(firstCellofLastCol, firstCellofLastCol.End(xlDown))
    'r.Cells.Select
    'MsgBox Str(WorksheetFunction.Count(r.Cells))
    
    criterialValue1 = InputBox("Enter The Project Code")
    
    Set selectedRange = activeSheet.Range(firtColName + ":" + lastColName)
    selectedRange.AutoFilter Field:=1, Criteria1:=criterialValue1
    selectedRange.AutoFilter Field:=2, Criteria1:=criterialCell2.Value
    
    Set r2 = activeSheet.Range(firstCellofLastCol.Offset(1, 0), firstCellofLastCol.End(xlDown))
    
    'j = WorksheetFunction.Count(r2.Cells.SpecialCells(xlCellTypeVisible))
    j = WorksheetFunction.CountA(r2.Cells.SpecialCells(xlCellTypeVisible))
    'MsgBox Str(j)
    
    If j = 0 Then
        MsgBox "The ICD was not found"
    ElseIf j > 0 Then
        Set fltrdrng = Intersect(activeSheet.UsedRange, activeSheet.UsedRange.Offset(1)).SpecialCells(xlCellTypeVisible)
        lUpper = UBound(Split(fltrdrng.Address, "$"))
    
        LstRow2 = Split(fltrdrng.Address, "$")(lUpper)
        activeSheet.Range("A2:" + lastColName & LstRow2).Copy (Sheets("TempAvail").Range("A1"))
     End If
     
     activeSheet.AutoFilterMode = False
End Sub

PS : I don't know why but WorksheetFunction.Count doesn't work for me : I replaced it by WorksheetFunction.CountA