Row count a filtered sheet
 

I have written a code that will filters a sheet however I now need to
count the rows that come out of the filter. Is there any way to do
this?

Thanks,

Peter

Row count a filtered sheet
 

Sub CountVisibleRows()
Dim rng As Range, rng1 As Range
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
Set rng1 = rng.SpecialCells(xlVisible)
MsgBox rng1.Count - 1 & " rows are visible"
End Sub




another way is using the Subtotal command. If a specific column will have
values in every row

Sub countvisibleRows2()
Dim rng As Range, cnt As Long
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
cnt = Application.Subtotal(3, rng)
MsgBox cnt - 1 & " rows are visible"
End Sub

--
Regards,
Tom Ogilvy

"Peter" wrote:

I have written a code that will filters a sheet however I now need to
count the rows that come out of the filter. Is there any way to do
this?

Thanks,

Peter

Row count a filtered sheet
 

rng.SpecialCells(xlCellTypeVisible).Rows.Count

where rng is the range that you have filtered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Peter" wrote in message
ps.com...
I have written a code that will filters a sheet however I now need to
count the rows that come out of the filter. Is there any way to do
this?

Thanks,

Peter

Row count a filtered sheet
 

On 5 Feb, 14:39, Tom Ogilvy
wrote:
Sub CountVisibleRows()
Dim rng As Range, rng1 As Range
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
Set rng1 = rng.SpecialCells(xlVisible)
MsgBox rng1.Count - 1 & " rows are visible"
End Sub

another way is using the Subtotal command. If a specific column will have
values in every row

Sub countvisibleRows2()
Dim rng As Range, cnt As Long
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
cnt = Application.Subtotal(3, rng)
MsgBox cnt - 1 & " rows are visible"
End Sub

--
Regards,
Tom Ogilvy



"Peter" wrote:
I have written a code that will filters a sheet however I now need to
count the rows that come out of the filter. Is there any way to do
this?

Thanks,

Peter- Hide quoted text -

- Show quoted text -

Tom that was brilliant!

Row count a filtered sheet
 

Hi Tom

I was looking through discussion groups to find the same thing, and came
across this, so I didn't have to post a question. Thanks very much, people
like you make such a difference to VBA hopelesses like me, your'e a star.
I've rated the post as helpful.

Aehan

"Tom Ogilvy" wrote:


Sub CountVisibleRows()
Dim rng As Range, rng1 As Range
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
Set rng1 = rng.SpecialCells(xlVisible)
MsgBox rng1.Count - 1 & " rows are visible"
End Sub




another way is using the Subtotal command. If a specific column will have
values in every row

Sub countvisibleRows2()
Dim rng As Range, cnt As Long
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
cnt = Application.Subtotal(3, rng)
MsgBox cnt - 1 & " rows are visible"
End Sub

--
Regards,
Tom Ogilvy

"Peter" wrote:

I have written a code that will filters a sheet however I now need to
count the rows that come out of the filter. Is there any way to do
this?

Thanks,

Peter