Auto Filter where range includes cells with a value of ""

[Reddog94]

I am using an auto filter criteria range, where the cells reference filter selections input on a different tab. For example, the cells in the criteria range have a formula similar to this:

=if(Sheet1!A1="","",Sheet1!A1)

However, if only 1 of the 4 input selections are chosen (which is often the case), when I run the auto-filter all lines are hidden. For example, I want to see all lines of data for the month of January:

Filter on Department: <blank>
Filter on Year: <blank>
Filter on Month: JAN
Filter on Description: <blank>

Unless there is a selection for all 4, all lines are hidden. I think it is because it is looking to filter on cells that have "", which of course nothing in the filter range has. Is there another way to either write the if formula, or a way to tell the auto-filter to ignore blank cells (that aren't really 'blank", because they have the "" in them)?

If I remove the formula in the cells that have no selection, the auto-filter works fine but that kind of defeats the purpose. The code I'm using is:

Sub AutoFilter2()
    Dim rngCritLabor As Range
    Dim rngFilterLabor As Range
    
    With Sheets("Labor")
        Set rngCritLabor = .Range("Filter_Crit_Labor")
        Set rngFilterLabor = .Range("Filter_Data_Labor")
    End With
    
    rngFilterLabor.AdvancedFilter _
        Action:=xlFilterInPlace, _
        criteriarange:=rngCritLabor
                    
End Sub

 

[BenMiller]

Why don't you have it return a zero, and hide the zero in options? Would that work?

 

[Reddog94]

That would probably work, except I don't know how to hide the 0 in options...can you tell me please?

 

[BenMiller]

What version of Excel are you using?

 

[Reddog94]

Oh sorry - 2007.

 

[BenMiller]

I don't know ... In 2010, go to File>Options>Advanced and under the Display options for this worksheet group, uncheck Show a zero in cells that have a zero value.
Or you could format the cells as Accounting (and select zero decimal places, if you want).

 

[Reddog94]

I thought you mean options in the auto-filter. Unfortunately shows zeros is already unchecked and the format doesn't seem to impact the auto-filter issue. I did narrow it down to being related to data lines in the filter range that have blanks or zeros. Still not sure how to resolve though. Thanks for your suggestions.

 

[Firefly2012]

You need to tweak your formula so that it returns all records in the respective column for that criteria value. If the column contains text, then rather than ="", your formula needs to return ="=*". If numerics (eg Year) then perhaps =">0"