Macro to read cell and hide all rows where the text occurs less than 10 times in the sheet

Hi All,

I have a spreadsheet with thousands of entries that I refresh each month and review new occurences of a particular behavior.

Each occurrence is a row with various identifying information, but the column I'm interested in is the OwnerName. I start by autofiltering out everything that didn't occur this month.

From there, I only review a set of occurrences of this behavior if the person has done it more than 10 times in a month. What I would like to do is create a macro that reads how many times a name (for example "Adam A. LastName") occurs in column G, and if it occurs fewer than 10 times, I want to hide the entire row.

I know the general format to hide the row. A "For/Next" loop that reads the text in the cell and If cell.Value (appears fewer than 10 times) Then cell.EntireRow.Hidden = True.

It's just getting it to automatically count how many times each name appears that I can't figure out. Do I need to create an array of all the names first?

Any help is greatly appreciated.

Thanks,
Carol

Hi

If you add another "helper" column, you could use SUMPRODUCT to get a count of the name for the given month. Then as part of the autofiltering, you could filter that column for entries that are >= 10.

If you give us an example file showing your structure and some sample data, then we could come up with a formula for you.

rylo

Thanks, here is a small sample with the gist of what I need. I need to hide all rows where the OwnerName appears less than 10 times.

Sample.xlsx

So what I came up with is =SUMPRODUCT(--(A$2:A$100=B2))

That way the range stays absolute, but it will always look at the correct name cell. I think this fixes it!

Thanks!