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