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

number of rows returned by autofilter?
 

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

number of rows returned by autofilter?
 

Thanks, Dave.

Exactly what I need.

"Dave Peterson" wrote:

Option Explicit
Sub testme()

number of rows returned by autofilter?
 

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

number of rows returned by autofilter?
 

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

number of rows returned by autofilter?
 

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

number of rows returned by autofilter?
 

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.

number of rows returned by autofilter?
 

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

number of rows returned by autofilter?
 

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