# excel filter result gone crazy

#### tony567

##### Well-known Member
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### antoka05

##### New Member
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
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
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
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
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,164
Messages
5,835,745
Members
430,384
Latest member
kiuwai

### 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.

### Which adblocker are you using?

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

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