If Statement Failing

[Ark68]

Consider the code below.
Worksheet 'ws_sand' is filtered (column A) to include all dates between two date ranges (f_lr & f_ur).
Rows 1 and 2 are static header rows, Row 2 holds the column (field) names.
For the most part this is working, except when there are no dates found as a result of the filter. There is no data displayed below row 2.
The line purple is meant to identify this scenario, but it is not. In my testing, when there are no rows returned as part of the filter, I am NOT getting the msgbox displayed and the nfm counter is not increased.

What must I do to improve my code to have this work as desired?

With ws_sand
        .AutoFilterMode = False 'turn off autofilter if on
        'f_lr = ws_sheet2.Cells(i, 11)
        'f_ur = ws_sheet2.Cells(i, 12)
        last_col = .Range("A1").CurrentRegion.Columns.Count
        Set f_range = .Range(.Cells(2, 1), .Cells(2, last_col))
        With f_range
            .AutoFilter field:=1, Criteria1:=">=" & f_lr, Operator:=xlAnd, Criteria2:="<=" & f_ur
        End With
        With ws_sand
            If .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row = 1 Then
                MsgBox "No sand records.", vbInformation, "NO SAND USED"
                nfm = nfm + 1
            End If
        End With
End With

 

[Fluff]

How about

If .Range("A1", .Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).CountLarge = 2 Then

 

[Roger Govier]

When I deal with the same type of thing working with tables I use something like

With .ListObjects("tblTemplate").DataBodyRange

     x3 = .SpecialCells(12).Rows.Count
     If Err.Number <> 0 Then
     Msgbox ("error etc.")

 

[xenou]

With ws_sand
            If .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row = 1 Then

Note that It seems you are looking at your headers with this code, which would always be visible and the top row will always be 1. As per other suggestions you would need to be doing more than just checking the first row ...

i.e, Fluff's solution which is checking if only two rows are visible on the worksheet looks fine.

 

[Ark68]

Thank you all for your suggestions.
"countlarge" is something new to me. I'll have to look deeper into that.

 

[Fluff]

You're welcome & thanks for the feedback