Advance Filter on cells with formula

[samahiji]

Hi
All cells in my sheet contains formula. I'm trying to use the advance filter to filter out the non blank cells "<>". However, it seems that The advance filter doesn't recognize cell as empty while it has a formula.

I'm using the following VBA:

Sub CreateAdvancedFilter()
   Dim rngDatabase As Range
   Dim rngCriteria As Range
'define the database and criteria ranges
   Set rngDatabase = Sheets("Details").Range("A1:BS2700")
   Set rngCriteria = Sheets("ADF").Range("A1:BS2")
'filter the database using the criteria
   rngDatabase.AdvancedFilter xlFilterInPlace, rngCriteria
   Sheets("Details").Activate
End Sub

Any thought?

 

[ChrisFoster]

The Advanced Filter feature does not recognize cells with formulas as blank. To filter for non-blank cells that contain formulas, you can use a helper column that checks if the cell has a value or not.

Here's an example of how to do this:

1. Insert a new column in your sheet.
2. In the first cell of the new column, enter the formula: =IF(ISBLANK(A1),"",IFERROR(1/(1/A1),""))
3. Copy the formula down to the bottom of your data range.
4. Select the entire data range including the new column.
5. Use the Advanced Filter feature to filter for values greater than 0 in the new column.
6. Delete the helper column once you have applied the filter.

In your VBA code, you can modify the range for the database to include the helper column you just created. For example, if the helper column is in column C, you can update the range to be "A1:C10000".

 

[samahiji]

Thanks a lot. Is it possible to have that helper column imbedded in vba? It is difficult to insert a helper column in my dataset.
or can you modify the above functionto be imbedded in my function below:

=IFNA(VLOOKUP($A2,Test!$B:$G,6,FALSE),"")

 

[ChrisFoster]

One way to get around this issue is to modify your VBA code to copy the range with formulas to a temporary range, replace all formulas with their values, and then apply the Advanced Filter to the temporary range.

Try this.

Sub CreateAdvancedFilter()

Dim rngDatabase As Range
Dim rngCriteria As Range
Dim rngTemp As Range

Set rngDatabase = Sheets("Details").Range("A1:BS2700")
Set rngCriteria = Sheets("ADF").Range("A1:BS2")

Set rngTemp = Sheets("Details").Range("BX1")
rngDatabase.Copy
rngTemp.PasteSpecial xlPasteValues

rngTemp.AdvancedFilter xlFilterInPlace,rngCriteria

rngTemp.Copy
rngDatabase.PasteSpecial xlPasteValues

rngTemp.ClearContents

Sheets("Details").

Activate End Sub

Note: The temporary range (in this case, "BX1") should be an unused range on the sheet. If "BX1" is already in use, you can modify the code to use a different range.

 

[RoryA]

There are various ways round it. If you are filtering text values for example, you could use ?* as the criterion, rather than <> If the values could be text or numbers in the same column then you can either use formula criteria or use two rows - one for the text criteria and one for the numbers.

 

[samahiji]

There are various ways round it. If you are filtering text values for example, you could use ?* as the criterion, rather than <> If the values could be text or numbers in the same column then you can either use formula criteria or use two rows - one for the text criteria and one for the numbers.

RoryA
Perfect! It was easy & quick way to solve the issue

I wish if you elaborate more on using formula criteria, what it mean (Give examples if possible), because I have noticed when two rows or more were used the filtering speed is affected.

 

[RoryA]

To use a formula for the criterion you need a header cell that does not match any of the headers in your table, then you write a formula based on the first row in your data using relative referencing. So for example, if you wanted to test for cells that contain something in column A, your criterion formula might be:

=LEN(A2)>0

The advanced filter will then evaluate this formula against every row in column A.