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
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:

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
0
scsymeCommented:
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

0
JagwarmanAuthor Commented:
File attached
filter-and-count.xlsm
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

scsymeCommented:
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
0
Rob HensonFinance AnalystCommented:
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
0
FarWestCommented:
Also you can use COUNTIF excel function
. make the inputbox put the input value in a cell and use its value as the criteria
0
gowflowCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
gowflow. Brilliant thanks
0
JagwarmanAuthor Commented:
a great expert, always delivers
0
gowflowCommented:
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
0
JagwarmanAuthor 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"
0
JagwarmanAuthor 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
0
gowflowCommented:
Can you post the link to the question here ?
gowflow
0
scsymeCommented:
I appreciate the acknowledgement. Glad you got what you needed :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.