Help with counting visible rows after filter in excel

[lilp32]

I am trying to set up a prompt to print only if filtered rows are visible. I need to count the visible rows after the filter is applied (not including the header). I have tried various combinations but nothing seems to work every time.

Here is what I have:

ActiveSheet.Range("$A$1:$BU$87").AutoFilter Field:=17, Criteria1:="="
ActiveSheet.Range("$A$1:$BU$87").AutoFilter Field:=1, Criteria1:="="
    If ActiveSheet.AutoFilter.Range.Columns(2).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 1 Then
    Yprint = MsgBox("There are new patients to enroll.  Do you want to print today's list?", vbYesNo + vbQuestion, "Print")
    If Yprint = vbYes Then
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
End If
Else: MsgBox ("No new patients today")
End If
 

[NeoPa]

If you use Ctrl-End when there are no rows except the header then the .Row of that resultant cell will be 1 (or whatever row your header is on).

If ActiveCell.SpecialCells(xlLastCell).Row = 1 Then
    Debug.Print "No Rows";
Else
    Debug.Print "Some Rows";
End If

NB. Please only use indenting in code if you do it in a way that makes sense. Indented code in the wrong places is even harder to read and work with than completely non-indented code - which is more than bad enough.

[fredlo2010]

Use subtotal with the count function to get that. and subtract 1 to remove the header.

dim count as long
 
count = worksheetfunction.subtotal(103, ActiveSheet.Range("$A$1:$BU$87").columns(1)) - 1
 

Hope it helps