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?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

antoka05

New Member
Joined
May 7, 2010
Messages
18
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,056
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

tony567

Well-known Member
Joined
Aug 23, 2008
Messages
515
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
 

tony567

Well-known Member
Joined
Aug 23, 2008
Messages
515
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...
 

Hooi

Board Regular
Joined
Jun 14, 2010
Messages
203
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

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
Top