number of rows returned by autofilter?

[Guest]

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]

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

 

[Guest]

Thanks, Dave.

Exactly what I need.

 

[STEVE BELL]

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

 

[Guest]

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

Thanks

 

[STEVE BELL]

Mark,

Glad to help...

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

 

[Guest]

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.

 

[Dave Peterson]

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

 

[STEVE BELL]

Noted!

Thanks...