Unable to filter nonblank cells using Mutiple criteria for Range Auto Filter

[dkum7526]

The below function that i have customized filter the blank cells using "=" as criteria, however it does not filter "<>" non blank cells. Please help.

Public Function apply_filter(wksht As Worksheet, filter_field As String, ParamArray criteria() As Variant) As Boolean

Dim fieldNo As Integer
Dim last_row As Integer

Set HeaderRow = wksht.Range("a1").EntireRow

'On Error GoTo error_hndler
fieldNo = match_columns(filter_field, HeaderRow)
apply_filter = HeaderRow.AutoFilter(Field:=fieldNo, Criteria1:=Array(criteria()), Operator:=xlFilterValues)
last_row = find_last_row(wksht)
If last_row > 1 Then
apply_filter = True
Else
apply_filter = False
End If


Exit Function

 

[zero269]

Dim fieldNo As Integer
Dim last_row As Integer

I'm not the best person to advise you on your primary request. However, it sounded interesting, so I took a look at how you wrote your code.
The one thing that stood out to me was that you're using the Integer Data Type for your fieldNo and last_row variables.
Considering the Integer only supports up to 32,767, I would at least change that to Long as it supports just over 2 million. This way you're not limited to only 32,767 rows, Column wise you're good according to this:

Excel specifications and limits - Microsoft Support

In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits.

support.microsoft.com

One thing I did was record a quick Macro that just filtered a column to exclude Blanks. This is what I got:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= "<>"

I'm not sure I noticed where your filtering BLANK cells based on what I'm seeing with my basic single-action recorded macro...
Hope this helps for what it's worth.

 

[Alex Blakenburg]

xlFilterValues is the equivalent to OR, if one of your criteria is <> (not blank) then all the other criteria using the same column become meaningless.

I haven't full been able to test this but try
replacing this:

apply_filter = HeaderRow.AutoFilter(Field:=fieldNo, Criteria1:=Array(criteria()), Operator:=xlFilterValues)

with this:

If criteria(0) = "<>" Then
    apply_filter = HeaderRow.AutoFilter(Field:=fieldNo, Criteria1:=Array(criteria()))
Else
    apply_filter = HeaderRow.AutoFilter(Field:=fieldNo, Criteria1:=Array(criteria()), Operator:=xlFilterValues)
End If

 

[dkum7526]

xlFilterValues is the equivalent to OR, if one of your criteria is <> (not blank) then all the other criteria using the same column become meaningless.

I haven't full been able to test this but try
replacing this:

apply_filter = HeaderRow.AutoFilter(Field:=fieldNo, Criteria1:=Array(criteria()), Operator:=xlFilterValues)

with this:

If criteria(0) = "<>" Then
    apply_filter = HeaderRow.AutoFilter(Field:=fieldNo, Criteria1:=Array(criteria()))
Else
    apply_filter = HeaderRow.AutoFilter(Field:=fieldNo, Criteria1:=Array(criteria()), Operator:=xlFilterValues)
End If

Thank you very much for your help. I got it now that xlFilterValues actually means to filter values i.e non blank values. Appreciate your help.