excel filter result gone crazy

tony567

Well-known Member
Joined
Aug 23, 2008
Messages
515
Cell contains like this:

B5 No
B6 1
B7 =SUBTOTAL(2,$B$5:$B6)
B8 =SUBTOTAL(2,$B$5:$B7)
B9 =SUBTOTAL(2,$B$5:$B8)
B10 =SUBTOTAL(2,$B$5:$B9)
B11 =SUBTOTAL(2,$B$5:$B10)
B12 =SUBTOTAL(2,$B$5:$B11)
B13 =SUBTOTAL(2,$B$5:$B12)
B14 =SUBTOTAL(2,$B$5:$B13)
B15 =SUBTOTAL(2,$B$5:$B14)
B16 =SUBTOTAL(2,$B$5:$B15)


C5 Month
C6 Apr
C7 Apr
C8 May
C9 May
C10 Jun
C11 Jun
C12 Jul
C13 Jul
C14 Aug
C15 Aug
C16 Aug


Ok, now create Data filter for range "B5:C16"
and the do Text Filter in column C for "Apr"

and see what happen, it gives me wrong result, because the result including month of "Aug"!

this is what i got:

No Month
1 Apr
1 Apr
1 Aug


is it weird, what's wrong here?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
May be some rows at the end of your data range are not correctly filtered.
Try to remove filters and apply new filters selecting all the rows.

Regards,
Antonio
 
Upvote 0
That is weird. It seems to be assuming that if the last row has a subtotal formula in it, it shouldn't be part of the filter. (The contents of the Aug cell don't actually matter - they can be anything). If you don't include the SUBTOTAL column in the filter, it seems to work.
 
Last edited:
Upvote 0
May be some rows at the end of your data range are not correctly filtered.
Try to remove filters and apply new filters selecting all the rows.

Regards,
Antonio

Have you tried?

or try this macro:

Code:
Sub Macro1()
    Range("B5").Value = "No."
    Range("C5").Value = "Month"
    Range("B6").Value = 1
    Range("B7:B16").Formula = "=SUBTOTAL(2,$B$5:$B6)"
    Range("C6").Value = "Apr"
    Range("C7").Value = "Apr"
    Range("C8").Value = "Mei"
    Range("C9").Value = "Mei"
    Range("C10").Value = "Jun"
    Range("C11").Value = "Jun"
    Range("C12").Value = "Jul"
    Range("C13").Value = "Jul"
    Range("C14").Value = "Aug"
    Range("C15").Value = "Aug"
    Range("C16").Value = "Aug"
    Range("B5:C16").Select
    Selection.AutoFilter
    ActiveSheet.Range("$B$5:$C$16").AutoFilter Field:=2, Criteria1:="Apr"
End Sub
 
Upvote 0
That is weird. It seems to be assuming that if the last row has a subtotal formula in it, it shouldn't be part of the filter. (The contents of the Aug cell don't actually matter - they can be anything). If you don't include the SUBTOTAL column in the filter, it seems to work.

I am so glad somebody have take a look at this...
 
Upvote 0
Haha it's weird u can see that the filter only select until C15 but not C16...if copy the value of the 2 column and paste it beside with value only the autofilter run normal...so weird
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top