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

"Jim May" wrote in message
news:zy7hd.1076$ep3.917@lakeread02...
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?

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



--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:zy7hd.1076$ep3.917@lakeread02...
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

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

"Tom Ogilvy" wrote in message
...
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



--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:zy7hd.1076$ep3.917@lakeread02...
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