Count Visible Cells when using Filters

[Canlink]

I would like to count only the visible cells in order to set the
number of pages when using filters on a database.

Has anyone reasoned out the correct coding?

 

[ryguy7272]

Since you posted in the Excel Programming area, I think this may not really
be what you want, but you didn't give a whole lot of information, or
requirements, so this may actually work for you.
http://www.techonthenet.com/excel/formulas/subtotal.php

Regards,
Ryan--

 

[Gary Keramidas]

you can try something like this. in this example, my column headers are in row
5, columns A thru U on sheet1.

Option Explicit
Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Dim filtlastrow As Long
With ws.Range("A5:U" & lastrow)
.AutoFilter field:=1, Criteria1:="01"
filtlastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'nothing but headers are visible
Else
MsgBox ws.Range("A6:A" & _
filtlastrow).SpecialCells(xlCellTypeVisible).Count
End If
End With
End Sub

 

[Canlink]

you can try something like this. in this example, my column headers are in row
5, columns A thru U on sheet1.

Option Explicit
Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Dim filtlastrow As Long
With ws.Range("A5:U" & lastrow)
.AutoFilter field:=1, Criteria1:="01"
filtlastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count= 1 Then
'nothing but headers arevisible
Else
MsgBox ws.Range("A6:A" & _
filtlastrow).SpecialCells(xlCellTypeVisible).Count
End If
End With
End Sub

Gary:
The coding is almost what I am looking for and I think I can modify
your suggestion to count the visible rows in a filtered database so I
can set the number of pages that will be printing showing this
selection.
Thanks
Geoff