Counting the number of visible rows after autofilter

Question

How do you count the number of visible/not null rows (from row 3 onwards, checking if column A is empty) after an autofilter? Right now I am only getting 26...

Full code:

Sub GetPrimaryContacts()

Dim Col As New Collection
Dim itm
Dim i As Long
Dim CellVell As Variant

'Get last row value
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

'Loop between all rows to get unique values
For i = 3 To LastRow
    CellVal = Sheets("Master").Range("F" & i).Value
    On Error Resume Next
    Col.Add CellVal, Chr(34) & CellVal & Chr(34)
    On Error GoTo 0
Next i

' Create workbooks - Token Not activated
Call TokenNotActivated
For Each itm In Col
    ActiveSheet.Range("A2:Z2").Select
    Selection.AutoFilter Field:=6, Criteria1:=itm
    Call CountFilterAreaRows
Next

End Sub

If Row 3 is hidden, it doesn't have any Visible cells... Perhaps you are trying to get the first visible row? — user2140261, May 10 '14 at 14:07
See update to the question I managed to do some debugging but at the moment lcount is not updating after the first error — theshizy, May 10 '14 at 14:09
Criteria1:=itm should be after row 3. Because when you filter the value if it does not exit then error occurred. And also set the range for col. — Prabhat Srivastava, May 10 '14 at 14:11

score 2 · Accepted Answer · edited May 23 '17 at 11:57

2

Here's a function that will count the visible rows in an autofiltered range, even if there are none:

Function CountFilterAreaRows(ws As Excel.Worksheet) As Long
Dim FilterArea As Excel.Range
Dim RowsCount As Long

Set ws = ActiveSheet
For Each FilterArea In ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    RowsCount = RowsCount + FilterArea.Rows.Count
Next FilterArea
'don't count the header
RowsCount = RowsCount - 1
CountFilterAreaRows = RowsCount
End Function

To call it as a function, see the edits above. Using your example you would could call it something like this (Untested):

Sub UseIt()
Dim ws As Excel.Worksheet
Dim itm
Dim col As Collection

'... your col logic

For Each itm In col
Set ws = ActiveSheet
    ActiveSheet.Range("A2:Z2").AutoFilter Field:=6, Criteria1:=itm
    Debug.Print CountFilterAreaRows(ws)
Next itm
End Sub

Note that you should avoid the use of Select.

edited May 23 '17 at 11:57

Community

1
1

answered May 10 '14 at 14:23

Doug Glancy

27,214
6
67
115

Thanks - how would I call it from my function (I've updated the question)? – theshizy May 10 '14 at 14:32
I get an "expected end function" error message when running it (happening in the CountFilterAreaRows function at EndSub) – theshizy May 10 '14 at 14:53
Woops, I just edited the function (on my phone). Hopefully it works now. – Doug Glancy May 10 '14 at 16:05

score 1 · Answer 2 · answered May 10 '14 at 14:10

1

I could be wrong, because I am guessing at what your code is actually doing, but see if this gets what you want done.

For Each itm In Col
    RowCount = Sheets("Master").Rows(itm.Row).Count
    MsgBox RowCount 
Next

answered May 10 '14 at 14:10

user2140261

7,855
7
32
45

OK I thought Col was a column, not Collection. – user2140261 May 11 '14 at 17:01

Gary's Student · Answer 3 · 2014-05-10T14:35:43.390

1

Say we have an AutoFilter with the first row containing headers and nothing below the filtered table:

Sub visiCount()
    Dim r As Range, n as Long
    n = Cells(Rows.Count, 1).End(xlUp).Row
    Set r = Range("A1:A" & n).Cells.SpecialCells(xlCellTypeVisible)
    MsgBox r.Count - 1
End Sub

EDIT ............started at A1 rather than A2

edited May 10 '14 at 14:35

answered May 10 '14 at 14:20

Gary's Student

95,722
10
59
99

I get an overflow error message when there no rows to count – theshizy May 10 '14 at 14:28

Counting the number of visible rows after autofilter

3 Answers3