Macro using .AutoFilter only works on first iteration--all other totals are zero

Question

I have two worksheets in an Excel 2016 (win 10) worksheet: data and template. Data contains the CLASS and related data. The sheet is sorted (a-z) by CLASS. Template contains background data with one row per class. An example of a CLASS might be oranges, apples, blackberries.

I traverse the list of classes in Template. For each class, I use AutoFilter to select a particular class on the "data" sheet and then count the number of entries with .SpecialCells(XlCellTypeVisible).Rows.count. I save the count to the Template sheet. The first class is filtered and counted correctly. All of the following classes are filtered correctly, but the count is zero.

Example:

"data" sheet
CLASS column (sorted a-z)
apples
apples
apples
apples
apples
blackberries
blackberries
blackberries
oranges
oranges

"Template" sheet
CLASS column
apples
blackberries
melons
oranges

Desired result:

"Template" sheet
CLASS column    myCount column
apples          5
blackberries    3
melons          0
oranges         2

Actual results:

"Template" sheet
CLASS column    myCount column
apples          5
blackberries    0
melons          0
oranges         0

My code is:

Sub CountAll()
  Dim rng As Range
  Dim myRow As Integer     'Row number in Template
  Dim thisClass As String  'Class we are currently counting
  Dim myCount As Integer   'Number of times thisClass is in "data"
  Dim numClasses as Integer  'Set to the number of classes in "Template"
  ...
  For myRow = 1 to numClasses
    thisClass = Worksheets("template").Cells(myRow, 1).Value
    Set rng = Worksheets("data").Range("A:A")
    With rng
      .AutoFilter Field:=1, Criterial:=thisClass
    End With

    myCount = rng.SpecialCells(xlCellTypeVisible).Rows.count
    Worksheets("template").Cells(myRow,3).Value = myCount
    ActiveSheet.showAllData 'Display all data again.
  Next myRow
End Sub

This code is temporary (6-12 months) until a report generator is purchased. VBA was not my choice. I'm better with assembly, c, FORTRAN, and JAVA. Any suggestions would be greatly appreciated. I have spent about 4 hours researching this problem.

Have your stepped through the code using F8? You may find your filters don't behave like you're expecting or a different sheet is the active. If you only want a count then you could also use countif. — ProfoundlyOblivious, May 11 '19 at 04:58
I am not familiar with F8 but I used a MsgBox to pause and show me values (see below). With the "data" sheet open, I can see that the filtering works correctly. However, after the first iteration, all of the counts are zero. — lac, May 14 '19 at 14:08
Gotcha. Pretty sure I have your solution and will post an answer. F8 and F9 are your debugging friends. F9 puts a breakpoint on a line which pauses execution so you can evaluate variables and conditions. F8 executes the next line and pauses. So in this situation, you could replace your msgbox with a breakpoint. — ProfoundlyOblivious, May 17 '19 at 18:36

ProfoundlyOblivious · Answer 1 · 2019-05-17T19:15:13.177

Check out this answer: Row count on the Filtered data

[Rows.Count] will only count the number of rows in the first contiguous visible area of the autofiltered range... [Cells.Count] will give you an accurate count even if the Range has multiple Areas, unlike the Rows property

Just tested and can confirm this is true. You have to be mindful with Cells.Count and ensure your range is handled appropriately.

In the linked solution, Cells.Count -1 is used to adjust for the title in the first row but you would not subtract 1 from the count if your range was based on a table's DataBodyRange.
If you're range contains more than 1 column then your count will be multiplied by the number of visible columns and you may want to divide the result by your column count or set a different range.

In your example, your range is explicitly defined as one full column but I am left to infer if you use a title row or want that row counted. Assuming you do use it and you don't want to count it then -1 is appropriate.

To conclude, this is your solution: myCount = rng.SpecialCells(xlCellTypeVisible).Cells.Count-1

Macro using .AutoFilter only works on first iteration--all other totals are zero

1 Answers1