AutoFilter not recognizing values

[Reddog94]

I have an autofilter setup with 3 values: "Show", "Hide" and "Always". The show/hide values are dependent if formulas based on other cells.

I have two sheets with this filter (Template and Summary); the filter works fine on the Template tab, however on the Summary tab...

When filtered to only show values "Always" and "Show", the filter only recognizes some of the "Show" values. I have copy pasted the if formula from the rows that work to the rows that don't and verified the formatting is the same. There are no named ranges that may be screwing it up and no empty cells in the filter range.

If I manually unhide the rows in questions, then run the filter, the rows stay shown. However, if I run the filter to only show "Always", then run it again to show "Always" or "Show", then the rows in question disappear.

I also have vba assigned objects for show and hide (the same scripts are used for the Template sheet and the Summary sheet), but the issue persists when using the buttons.

The curious thing is that if, in the rows in question, I hard code "Show" instead of it being an if formula, the filter works fine. But the formula that doesn't work is also used on rows that do work, so I'm completely stumped.

Help...please!

 

[n0gel]

Please provide some more info. What version of Excel are you using, and show us the formula and code involved.

 

[Reddog94]

I am working in Excel 2007.

These are the scripts, however, I don't believe it to be an issue with the script because manually auto-filtering doesn't work properly either (and the scripts work just fine in another tab that is filtering exactly the same things).

Sub ShowDetail()

ActiveSheet.Unprotect
ActiveSheet.Range("$A$6:$A$399").AutoFilter Field:=1, Criteria1:="=ALWAYS" _
, Operator:=xlOr, Criteria2:="=SHOW"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub



Sub HideDetail()

ActiveSheet.Unprotect
ActiveSheet.Range("$A$6:$A$399").AutoFilter Field:=1, Criteria1:="ALWAYS"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

 

[n0gel]

The code in Sub ShowDetail() may have an extra "=". Look at the way this is written compared to the hide detail sub shown below.

Criteria1:="=ALWAYS"
Criteria2:="=SHOW"

Criteria1:="ALWAYS"

But, what about the If formula you were talking about?

 

[Reddog94]

I thought that was weird too, but that is from a recorded macro that I just copied into my script. I did try it without the extra = sign after the left parenthesis and both ways work identically (and both with the same issue).

For the if statement, it is basically saying this:
IF(K100<>0,"SHOW","HIDE")

This formula is used in both rows that are show/hiding properly, and in the rows that are the issue. I pasted it from rows that are working properly, so know that it is identical. There are no extra spaces, funky formatting, etc.

I don't have the rights to post an attachment, otherwise I would share a screen shot.

 

[n0gel]

I wouldget rid of the extra = sign, it's not correct whether it works or not.

I'm still not clear what you are doing with the If formula. Are you using it in a column specifically setup to filter on Show or Hide (a helper column)? And where is the "Always" criteria come into play?

 

[Reddog94]

Correct, column A in the sheet is the filter. It has 3 values:

Hard Coded:
"ALWAYS" - which means the row should always be shown (Sub HideDetail)

Formula Driven via the if<>0:
"HIDE" - no data is in the row, so never show it
"SHOW" - data is in the row, show it when detail is desired (Sub ShowDetail)

The filter options (in the script or manual) used are:

(1 - the HideDetail macro)
Only show if column A = "ALWAYS"

(2 - the ShowDetail macro)
Show if column A = either "ALWAYS" or "SHOW"

 

[n0gel]

Well, I've set up a sheet just like you have described and it works fine (of course). I did verify that the macro recorder puts in the extra = sign, and that it works fine without it.

The only thing left I can think of is the file type. Mine is a 2007 file type, not a .xls type. So I'm not in compatibility mode. In Excel 2007 you should be able to write the criteria like this

Criteria1:=Array("Always", "Show")

Other than that I'm stumped too, because it works for me.

 

[Reddog94]

Rats. Thank you very much for the assistance.
I will keep digging and post if I figure it out.

 

[n0gel]

Have a great holiday!