Selecting Ctrl+Shift+Down within a Count Function Macro

[lineian]

I have students doing a project where they collect data from a cemetery and record the age of the person and the year he/she died. It copies into a table where I have Macros that filter certain criteria (i.e. "Ages from 10-19" or "Death Year from 1880-1889"). I was hoping to be able to have a =COUNT formula that tells me how many people died after those filters have been used (i.e. count number of people who died within years 1880-1889 and were between ages 10-19). I guess I'm running into two problems:

1) After I filter my criteria, obviously the range of data that I wish to count changes depending on the criteria (could be 100 rows for one thing and 213 rows for another). I tried recording a macro that counts a ctrl+shift+down after I filter, but the code will only count the same number of cells, even after I add more data. The macro recorded a count of 3 deaths from 1810-1819 ages 0-9, but when I added another person and ran my macro filters, the =count function still showed 3 instead of 4.

2) Maybe I'm trying to do something too complicated. I noticed when I filter a table, the rows that don't qualify still exist and rather they just "disappear." The table will jump in rows going 5, 6, 7, 27. I only want to =count the people that are still showing; however, when I tried the =count function differently from problem (1) from above, is still included 5-27. This would be the whole table of values whether they are currently showing or not.

If someone has a solution to my problem (whether a different code I need to be using for my macros or possibly a different way of going about my data organization besides a table), please let me know!

 

[Veritan]

Hello lineian, welcome to the MrExcel forums. You could try this code if you like.

Option Explicit
Dim RowCount As Integer
Private Sub Count_Visible_Rows()
    Dim r As Range
    Range("A2").Select
    For Each r In Range("A2", Range("A2").End(xlDown))
        If r.EntireRow.Hidden = False Then RowCount = RowCount + 1
    Next r
End Sub
Sub Filtered_Count()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:= ">=1/1/1810", Operator:=xlAnd, Criteria2:="<=12/31/1819"
    Call Count_Visible_Rows
    Range("K10").Value = RowCount
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3
    RowCount = 0
End Sub

This assumes that Row 1 has your headers in it, starting at cell A1. Where I have "A2", you'll want to change that to match the first line of actual data you have. Also, I'm assuming all your data is contiguous with no blank rows between any records. Finally, where I have "D10", you'll want to make that whatever cell you want your data to be written to. This method also adds the filter for you. It's not necessary to include that. If you like, you can remove the lines dealing with the filter and just manually apply the filter yourself. However, if you want to use the automatic filtering approach, just copy the code in the Filtered_Count sub and keep adding it to the end of the code in that sub. Change the Field:= (the number for this is the column number that your criteria is in, counting from Column A being 1) and Criteria:= arguments to match what you need, and change the cell where you place the data to match each specific cell you want the information in.

An alternative approach is to use the SUBTOTAL function, if you'd prefer not to mess with coding. Just make sure you copy the value of the result from the function to the cell as a value, rather than leaving it as a formula, or the amount will change every time you filter your data. As a side note on filtering, all it does is temporarily hide the rows that don't meet your criteria. Like you said, the rows are not gone or deleted, just not currently visible. SUBTOTAL and the code above both take this into account and won't count rows that are hidden.