VBA Help - If Filtered Range Return Nothing Then

[Johnny Thunder]

Hello all,

I am having some issues with a statement in my current code and all sources online aren't really answering my question directly.

I have a fairly long script that loops thru a range to get a filter criteria value and then apply the filter to a range on a seperate sheet. This code works great but recently I added in some new values to the list of criteria that don't have any data on my filter range but will in the future.

I need a "IF Statement to determine if the Filter Range results in a nothing being returned and if so, GoTo Jump which will jump the code down past several other actions in the code and go to the next loop value.

Any ideas will be super helpful.

My filter range: ws.Range("B3:B" & LastR).SpecialCells(xlCellTypeVisible)

 

[Trebor76]

Hi there,

My range has the text "Green" so when I filtered on "Red" there are no matches:

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim LastR As Long
    
    Set ws = Sheets("Sheet1")
    LastR = 20

    With ws.Range("B3:B" & LastR)
        .AutoFilter Field:=1, Criteria1:="Red"
        If Application.WorksheetFunction.Subtotal(3, .SpecialCells(xlCellTypeVisible)) = 0 Then
            GoTo Jump
        End If
    End With
    
    MsgBox "Code from here if there are records in the filtered range."
    
Exit Sub
    
Jump:

    MsgBox "There were no filtered records"

End Sub

Regards,

Robert

 

[Peter_SSs]

My filter range: ws.Range("B3:B" & LastR).SpecialCells(xlCellTypeVisible)

If row 3 is the header row then try something like this. Any code between the asterisk lines will only be executed if there are visible results in the filtered range. If not, then the code will jump to the 'Code continues from here' line.

If your header row is actually row 2 then simply change the B3 in the code to B2.

If ws.Range("B3:B" & LastR).SpecialCells(xlCellTypeVisible).Count > 1 Then
  '*************
  'Your other actions go here
  '*************
End If

'Code continues from here

 

[Johnny Thunder]

Thanks Trebor and Peter! I ended up going with Peters solution since it was pretty straight forward and a quick modification. Worked like a charm. Cheers

 

[Peter_SSs]

You're welcome. Glad we could help.