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?

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

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" <(E-Mail Removed)> wrote in message
news:e5283797-10e0-441b-9754-(E-Mail Removed)...
>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?

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" <canl...@gmail.com> wrote in message
>
> news:e5283797-10e0-441b-9754-(E-Mail Removed)...
>
> >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