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

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

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

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!

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