PDA

View Full Version : count rows after worksheet is filtered



DarReNz
10-31-2005, 09:20 PM
Hi, from the workbook i have attached, as you can see in Input worksheet at F5 is a drop down list and when selected it will filters the Bau worksheet. What I want is, each time there is a selection from the drop down list, I wish to count how many rows are there in Bau worksheet and record the number of rows under Cases column in Param worksheet. I tried to use SUBTOTAL and SUMPRODUCT but it doesn't work. Is there a code that can help me ? Thanks.

HaHoBe
10-31-2005, 10:27 PM
Hi, DarReNz,

if I use the following formula in Param!C3 it shows the number of rows in Bau:

=SUBTOTAL(3,Bau!F:F)-1

Option Explicit

Sub DarReNz()
Dim lngLastRow As Long
Dim lngCounter As Long
Dim lngFound As Long

Application.ScreenUpdating = False
lngLastRow = Range("a1").End(xlDown).Row
For lngCounter = 2 To lngLastRow
If Rows(lngCounter).Hidden = False Then
lngFound = lngFound + 1
End If
Next lngCounter
Application.ScreenUpdating = True

MsgBox lngFound
End Sub
Ciao,
Holger

DarReNz
10-31-2005, 11:18 PM
Hi HaHoBe,

If I use SUBTOTAL formula in Param worksheet from C2 to C6, you will notice that all of them will give me the say result when worksheet is filtered from the drop down list(F5) in Input worksheet. How do i overcome this ?

HaHoBe
10-31-2005, 11:24 PM
Hi, DarReNz,

understood - please change the formula for Param!C2:C6 to read


=IF(Input!$F$5=B2,SUBTOTAL(3,Bau!F:F)-1,0)
Ciao,
Holger

DarReNz
10-31-2005, 11:38 PM
hi HaHoBe,

This will work only if i select from the F5 drop down list. What if I don't want to select from F5 and still want to know the results if i choose from the list ?
This is what I want

Cases
3
1
1
1
0

HaHoBe
10-31-2005, 11:47 PM
Hi, DarReNz,

could be solved by CounIf but that will definitely not reflect the results of the filtered list - starting with D2 it should read:


=COUNTIF(Bau!F:F,B2)
btw: I had to copy Cool to Sheet Param to get the proper result in Sheet Bau.

Ciao,
Holger

HaHoBe
11-01-2005, 12:20 AM
Hi, DarReNz,

maybe acw at Ozgrid could be more helpful if you explain the problem there more detailed.

Bye,
Holger

DarReNz
11-01-2005, 02:29 AM
Hi HaBoBe,

I have now updated the workbook. I have another question. How do I count how many rows for Pending, Waiting, Cancelled that Carolyn have as you can see in Param worksheet ? I want it the same way like how I did for Cases. Thanks

Bob Phillips
11-01-2005, 02:35 AM
One more shot in the light of what you say here.

Put this formula in Param worksheet, cell C2

=SUMPRODUCT((Bau!$F$2:$F$20=Param!B2)*SUBTOTAL(3,OFFSET(Bau!$F$1,ROW(Bau!$F $2:$F$20)-ROW(Bau!$F$1),,1)))

and copy down for all people.

Bob Phillips
11-01-2005, 02:58 AM
.

DarReNz
11-01-2005, 05:57 PM
hi xld,

This will work only if i select from the F7 drop down list. What if I don't want to select from F7 and still want to know the results if i had chosen from the list ? Meaning I want the all the entries displayed even if i did not select from the list.
Thanks

austenr
11-01-2005, 10:21 PM
This problem is cross posted at Ozgrid. It is not good practice to post somewhere else if people here are trying their best to help you. Please give them a chance before posting on another board or at the least, if you feel you are not getting what you need here close your post before posting on another board. Thanks

geekgirlau
11-02-2005, 01:53 AM
Can I ask why you're not using a pivot table for this? Seems to be crying out for one!

DarReNz
11-02-2005, 02:02 AM
yes i posted there as well as recommended by HaHoBe ...... it seems the formula used there is less complicated ...... i hope i don't offend anyone sorry ....

Bob Phillips
11-02-2005, 03:10 AM
yes i posted there as well as recommended by HaHoBe ...... it seems the formula used there is less complicated ...... i hope i don't offend anyone sorry ....

I have tried on both threads to help you with tjhis one, all along struggling to understand what was not working. IF you got an answer elsehwere common courtesy would suggest that you post back here and let us know that it is resolved rather than kepp asking and sending me PMs asking for more help.

Looking at the post on OzGrid, which you say is perfect, I cannot see mthe difference between the solution offer5ed there, and my first solution offered some days ago, apart from the dynamic ranges which are trivial. I found the whole trail of this misleading and unstructured, you introduction of SUBTOTAL for instance; it has not been an edifying experience.

Shazam
11-02-2005, 05:18 PM
Let me know this is the formula you are looking ? I put this formula in worksheet Param in cell C8 for and I also posted a attachment for you.

=SUMPRODUCT((Bau!F$2:F$100=Param!A$8)*(Bau!G$2:G$100=Param!B8)*(LEN(Bau!G$2 :G$100)<>0))

Also you'll need to clean up your data in your worksheets because the formulas are not calculating correctly. There are some unseen junk data in your workbook. So you need to use this code.


Sub Clean_Trim()

Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction
Set Func = Application.WorksheetFunction
On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub
For Each oCell In CleanTrimRg
oCell = Func.Clean(Func.Trim(oCell))
Next
End Sub

DarReNz
11-18-2005, 01:13 AM
Hi Shazam,

As you can see from the attachment how do i filter for the Start Date only for the month of October ? The column in Param worksheet which I want to find out is "OctoberCases". Thanks