Count Visible Cells when using Filters
 

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?

Count Visible Cells when using Filters
 

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--


--
RyGuy


"Canlink" wrote:

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?

Count Visible Cells when using Filters
 

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

--


Gary


"Canlink" wrote in message
...
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?

Count Visible Cells when using Filters
 

On Jun 12, 12:00 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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

"Canlink" wrote in message

...

I would like tocountonly thevisiblecellsin order to set the
number of pages whenusingfilterson a database.

Has anyone reasoned out the correct coding?

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