asked on

vba to filter, count and put result on different Sheet

Hi Experts, I have looked through the EE and there are quite a few examples of filter and count but,

I need to filter, then count the number of items in the filter and put the result onto Sheet1 in cell E6

i.e. on the active sheet when I filter column N with a date [that is input through an input box] lets say there are 8 items in the filter I need to then put 8 in sheet1 in cell E6

Hope someone can help.

Thanks

Jacques Geday

i.e. on the active sheet when I filter column N with a date [that is input through an input box] lets say there are 8 items in the filter I need to then put 8 in sheet1 in cell E6

How do you filter them thru VBA ?
Can you post the code or a workbook ?

gowflow

scsyme

If you are using an Excel table then you can use the SUBTOTAL function. For example if you have a table called MyTable that spans range A1:P100 and you filter column N, called MyDate, for a specific date then the following function will give the number of rows that are now visible in the table:

=SUBTOTAL(103,MyTable[MyDate])

Open in new window

Jagwarman

ASKER

File attached
filter-and-count.xlsm

scsyme

Put the following into cell E6 of sheet1 and I think you'll get the results that you are after:

=SUBTOTAL(103,Sheet2!N:N)-1

Open in new window

The -1 at the end is because the count will also include the header row. Note that if there is other 'noise' in the column outside the data range then you may not be able to use N:N

Rob Henson

Have the input box put the date value into a cell and then you can use the COUNTIF function in E6:

=COUNTIF(Sheet2,DateCell)

The data doesn't have to be filtered for this to work.

Thanks
Rob H