We help IT Professionals succeed at work.

vba to filter, count and put result on different Sheet

Jagwarman
Jagwarman asked
on
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
Comment
Watch Question


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

Commented:
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

Author

Commented:
File attached
filter-and-count.xlsm

Commented:
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 HensonFinance Analyst

Commented:
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

Commented:
Also you can use COUNTIF excel function
. make the inputbox put the input value in a cell and use its value as the criteria
Here it is.

I fixed your sub Filterit and modified it to suit what you want.

Sub filterIt()
Dim W1Startdate3 As String, W1Startdate4 As String, TempDate As String
Dim WS As Worksheet
Dim Rng As Range, cCell As Range
Dim MaxRow As Long, I As Long

Set WS = ActiveSheet
WS.Range("1:1").AutoFilter


Do
    W1Startdate3 = Application.InputBox("Enter the Third Date (Wednesday) in DDMMYY format")
    If W1Startdate3 <> "False" Then
        TempDate = Left(W1Startdate3, 2) & "/" & Mid(W1Startdate3, 3, 2) & "/" & Right(W1Startdate3, 2)
    End If
Loop Until IsDate(TempDate) Or W1Startdate3 = "False"

Do
    W1Startdate4 = Application.InputBox("Enter the Fourth Date (Thurday) in DDMMYY format")
    If W1Startdate4 <> "False" Then
        TempDate = Left(W1Startdate4, 2) & "/" & Mid(W1Startdate4, 3, 2) & "/" & Right(W1Startdate4, 2)
    End If
Loop Until IsDate(TempDate) Or W1Startdate4 = "False"


If W1Startdate3 = "False" Or W1Startdate4 = "False" Then
    WS.AutoFilterMode = False
    Exit Sub
End If

WS.UsedRange.AutoFilter Field:=14, Criteria1:="=" & W1Startdate3, Operator:=xlOr, Criteria2:="=" & W1Startdate4
MaxRow = WS.Range("N" & WS.Rows.Count).End(xlUp).Row
Set Rng = WS.Range("N1:N" & MaxRow).SpecialCells(xlCellTypeVisible)

If MaxRow > 1 Then
    For Each cCell In Rng
        I = I + 1
    Next cCell
Else
    I = 1
End If

Sheets("Sheet1").Range("E6") = I - 1
End Sub

Open in new window


But I think you will not get what you want as your date are not correctly formatted. They are not recognized as date in Excel DDMMYY but anyhow see if this is what you want. Each time activate the macro Filterit and give your answers to first and second Inputbox and see results in sheet1. If you press on the first or second Inputbox Cancel then you will get also no filtering as you need to have 2 values there.

Let me know.

See attached file as well.
gowflow
filter-and-count-V01.xlsm

Author

Commented:
gowflow. Brilliant thanks

Author

Commented:
a great expert, always delivers
Thank you and appreciate your comment. Pls feel free to advise if you need help for any other issue, will be glad to assist.
gowlfow

Author

Commented:
I have posted a new question which I am sure you will be able to help me with titled "copy data from several sheets into a summary sheet"

Author

Commented:
scsyme thanks for your input I am sure I will be able to use this in other files but I need VBA in this particular instance
Can you post the link to the question here ?
gowflow

Commented:
I appreciate the acknowledgement. Glad you got what you needed :-)