Question RE Count of records in AutoFilter

[Jim May]

Why is it that I'm having to use the separate Else statement "Count -2" when
my
I'm back to selecting "All" from my Auto-Filter range?

Private Sub CommandButton1_Click()
If FilterMode = True Then
MsgBox ActiveSheet.AutoFilter.Range.Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible).Count - 1
Else: MsgBox ActiveSheet.AutoFilter.Range.Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible).Count - 2
End If
End Sub

 

[Bob Phillips]

Why is it that I'm having to use the separate Else statement "Count -2" when
my
I'm back to selecting "All" from my Auto-Filter range?

Is there something missing in the previous paragraph Jim?

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
if activesheet.AutofilterMode then
MsgBox ActiveSheet.AutoFilter.Range.Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible).Count - 1
end if
End Sub

worked fine for me.
Check
Activesheet.Autofilter.Range.Address

 

[Jim May]

Thanks Tom:
Yeah, not sure why, but when (in the immediate window) I did the
? Autofilter.Range.Address
I got $A$10:$D$21 << where row 21 is in fact blank,,,,
Turned off auto filter - then re-instated,
back to auto filter "on" immediate window again
got $A$10:$D$20; solved!!
realize now I can just use single statement macro

Private Sub CommandButton1_Click()
MsgBox ActiveSheet.AutoFilter.Range.Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible).Count - 1
End Sub

Thanks,
Jim May