number of rows returned by autofilter?

Hello.

Is there a quick way to retrieve the number of rows returned in an
AutoFilter object?

For instance, if there were 20 rows of data, but when the autofilter
criteria are applied, only 5 rows are visible... to return that number, 5, in
code?

Thanks,
Mark

Option Explicit
Sub testme()

Dim rngF As Range

With ActiveSheet.AutoFilter.Range
Set rngF = .Columns(1).Cells.SpecialCells(xlCellTypeVisible)
If rngF.Cells.Count = 1 Then
'only the header is visible
MsgBox "no details shown"
Else
MsgBox rngF.Cells.Count - 1 & " rows of data visible"
End If
End With
End Sub

mark wrote:
>
> Hello.
>
> Is there a quick way to retrieve the number of rows returned in an
> AutoFilter object?
>
> For instance, if there were 20 rows of data, but when the autofilter
> criteria are applied, only 5 rows are visible... to return that number, 5, in
> code?
>
> Thanks,
> Mark

--

Dave Peterson

Thanks, Dave.

Exactly what I need.

"Dave Peterson" wrote:

> Option Explicit
> Sub testme()

Assuming that column A is part of the data

Dim x As Long, y As Long

x = Cells(Rows.Count, "A").End(xlUp).Row

y = Range(Cells(1, 1), Cells(x, 1)).SpecialCells(xlCellTypeVisible).Count
MsgBox y


--
steveB

Remove "AYN" from email to respond
"mark" <[email protected]> wrote in message
news:[email protected]...
> Hello.
>
> Is there a quick way to retrieve the number of rows returned in an
> AutoFilter object?
>
> For instance, if there were 20 rows of data, but when the autofilter
> criteria are applied, only 5 rows are visible... to return that number, 5,
> in
> code?
>
> Thanks,
> Mark

Yep, that would work too. Thanks. It was the
..SpecialCells(xlCellTypeVisible) that I needed to learn about.

Thanks

"STEVE BELL" wrote:

> Assuming that column A is part of the data
>
> Dim x As Long, y As Long
>
> x = Cells(Rows.Count, "A").End(xlUp).Row
>
> y = Range(Cells(1, 1), Cells(x, 1)).SpecialCells(xlCellTypeVisible).Count
> MsgBox y
>
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "mark" <[email protected]> wrote in message
> news:[email protected]...
> > Hello.
> >
> > Is there a quick way to retrieve the number of rows returned in an
> > AutoFilter object?
> >
> > For instance, if there were 20 rows of data, but when the autofilter
> > criteria are applied, only 5 rows are visible... to return that number, 5,
> > in
> > code?
> >
> > Thanks,
> > Mark
>
>
>

Mark,

Glad to help...

Picked that up from this ng.
Also got it by recording a Edit > Goto > Special > visible cells

--
steveB

Remove "AYN" from email to respond
"mark" <[email protected]> wrote in message
news:[email protected]...
> Yep, that would work too. Thanks. It was the
> .SpecialCells(xlCellTypeVisible) that I needed to learn about.
>
> Thanks
>
> "STEVE BELL" wrote:
>
>> Assuming that column A is part of the data
>>
>> Dim x As Long, y As Long
>>
>> x = Cells(Rows.Count, "A").End(xlUp).Row
>>
>> y = Range(Cells(1, 1), Cells(x, 1)).SpecialCells(xlCellTypeVisible).Count
>> MsgBox y
>>
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "mark" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello.
>> >
>> > Is there a quick way to retrieve the number of rows returned in an
>> > AutoFilter object?
>> >
>> > For instance, if there were 20 rows of data, but when the autofilter
>> > criteria are applied, only 5 rows are visible... to return that number,
>> > 5,
>> > in
>> > code?
>> >
>> > Thanks,
>> > Mark
>>
>>
>>

> Also got it by recording a Edit > Goto > Special > visible cells

Ahh, now that you mention it, I do remember exploring that menu item once...

But I wasn't thinking of that just now.

Some people put notes/other data after the autofilter range. If that's the
case, then this may give you incorrect results.



STEVE BELL wrote:
>
> Assuming that column A is part of the data
>
> Dim x As Long, y As Long
>
> x = Cells(Rows.Count, "A").End(xlUp).Row
>
> y = Range(Cells(1, 1), Cells(x, 1)).SpecialCells(xlCellTypeVisible).Count
> MsgBox y
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "mark" <[email protected]> wrote in message
> news:[email protected]...
> > Hello.
> >
> > Is there a quick way to retrieve the number of rows returned in an
> > AutoFilter object?
> >
> > For instance, if there were 20 rows of data, but when the autofilter
> > criteria are applied, only 5 rows are visible... to return that number, 5,
> > in
> > code?
> >
> > Thanks,
> > Mark

--

Dave Peterson

Noted!

Thanks...

--
steveB

Remove "AYN" from email to respond
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Some people put notes/other data after the autofilter range. If that's
> the
> case, then this may give you incorrect results.
>
>
>
> STEVE BELL wrote:
>>
>> Assuming that column A is part of the data
>>
>> Dim x As Long, y As Long
>>
>> x = Cells(Rows.Count, "A").End(xlUp).Row
>>
>> y = Range(Cells(1, 1), Cells(x, 1)).SpecialCells(xlCellTypeVisible).Count
>> MsgBox y
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "mark" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello.
>> >
>> > Is there a quick way to retrieve the number of rows returned in an
>> > AutoFilter object?
>> >
>> > For instance, if there were 20 rows of data, but when the autofilter
>> > criteria are applied, only 5 rows are visible... to return that number,
>> > 5,
>> > in
>> > code?
>> >
>> > Thanks,
>> > Mark
>
> --
>
> Dave Peterson