How to remove blank rows when using Excel VBA Autofilter

Question

I have written a macro that searches through workbook and applies an autofilter to any listobjects which have a column named "Code". However, when I apply the filter, it does not filter out the blank rows. Any idea on how I can filter these out?

Here is the code which applies the filter:

Public Sub ApplyFilter(filter As Variant)
Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject

Set  wb = ActiveWorkbook

' Loop through each sheet in the workbook
For Each ws In wb.Sheets
    ' Find any listobjects within the sheet
    For Each lo In ws.ListObjects
        Dim r As Integer
        ' Find the column named Code and filter on this column
        r = lo.Range.Rows(1).Find("Code").Column
        ' Clear any existing filter
        lo.Range.AutoFilter Field:=r
        ' If the filter code is not "All Categories", 999, apply the filter
        If filter(0) <> 999 Then
            lo.Range.AutoFilter Field:=r, Criteria1:=filter, Operator:=xlFilterValues
        End If
    Next
Next

End Sub

The filter that is passed in is an array which may just have one criteria, or many. I have also tried adding criteria2:="", but that did not change anything.

Let me know if you have any ideas. Thanks!

Here is the other related code:

Public Sub FilterInvoice(filter As Range)
    Me.ApplyFilter Me.BuildFilter(filter)
End Sub

Public Function BuildFilter(filter As Range) As Variant
    Dim r As Range
    Dim arFilter() As String

    ' Get the cell of the current category
    Set r = Range("Categories").Find(filter.Value)

    ' Set the initial filter value to the category id
    ReDim Preserve arFilter(1)
    arFilter(0) = r.Offset(0, -1).Value

    ' Find any child categories, add child id's to filter array
    For c = 1 To Application.CountIf(Range("Categories").Columns(3), arFilter(0))
        Dim PrevChild As Range
        ' Expand the filter array
        ReDim Preserve arFilter(c + 1)
        If c = 1 Then
            Set PrevChild = Range("Categories").Columns(3).Find(arFilter(0))
        Else
            ' If it is not the first time through the loop, look for the next child after PrevChild
            Set PrevChild = Range("Categories").Columns(3).Find(arFilter(0), PrevChild)
        End If
        ' Offset the found child to get its code, add it to the filter array
        arFilter(c) = PrevChild.Offset(, -2)
    Next

    ' Add "<>" and "<900" to the criteria list to hide blank rows
    'ReDim Preserve arFilter(UBound(arFilter) + 2)
    'arFilter(UBound(arFilter) - 1) = "<>"
    'arFilter(UBound(arFilter)) = "<900"

    'Return the filter array
    BuildFilter = arFilter
End Function

Reafidy · Accepted Answer · 2011-10-12 21:34:50Z

1

If you are filtering by multiple criteria using an array then by not including "=" the autofilter should filter the blanks. For example this will NOT filter blanks:

Criteria1:=Array("test", "2", "3", "4", "=")

Failing that you may need to hide them manually using specialcells(xlcelltypeblanks).

EDIT:

Okay I think I might have confused you there with my first solution. I have removed it.

Now that I can see your code I think what might be happening is that as you are looping through the range and adding your criteria you are probably adding a blank cell. Step through the loop one at a time and make sure this is not the case. You could add this to display the filter and make sure it does not contain blanks:

Debug.Print Join(arfilter, ",")

edited Oct 12, 2011 at 21:34

answered Oct 12, 2011 at 20:40

Reafidy

8,4896 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JoshPeltier Over a year ago

Would you mind expanding? I need one of the criteria to be my array containing the codes which I am filtering for. I tried using the array as criteria one and "<>" as criteria 2, it gave no results. If I leave the operator set to xlFilterValues, it gives the original results (with blank rows). Out of curiosity what does the "<>" criteria mean?

Reafidy Over a year ago

If you need more help then please show me the code which populates the actual filter you are using and calls the applyfilter sub routine.

JoshPeltier Over a year ago

Guess it will be by hand. I change by build-filter code to add both "<>" and "<900" to the end of the array and it kept giving me funky results with both xlAnd and xlFilterValues. Thanks for the input though!

JoshPeltier Over a year ago

You are right. I needed my first redim preserve to be redim preserve arfilter(0), not arfilter(1). Thanks for the help!

fro zen · Accepted Answer · 2019-01-10 07:31:37Z

I know this is an old question but I coudln't find any satisfying answer anywhere on the Internet

So I'd like to share a solution which seems to work pretty well:

ActiveSheet.Range("$A$1:$V$3000").AutoFilter Field:=ActiveSheet.Range("$A$1:$V$1").Find("Monitoring").Column, _
Criteria1:="<>Done", Operator:=xlFilterValues

blank cells are still present so we need no filter them out

ActiveSheet.Range("$A$1:$V$1").Find("Monitoring").EntireColumn.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

Of course because it uses xlFilterValues you can use an Array filter as well

ActiveSheet.Range("$A$1:$V$3000").AutoFilter Field:=ActiveSheet.Range("$A$1:$V$1").Find("Monitoring").Column, _
Criteria1:=Array( _
     "Department1", "Department2", "Department3", "Department4", _
    "Department5", "Department6", "="), Operator:=xlFilterValues

Hope you enjoy!

Collectives™ on Stack Overflow

How to remove blank rows when using Excel VBA Autofilter

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related