title: How to remove blank rows when using Excel VBA Autofilter
body: <p>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?</p>
  
  <p>Here is the code which applies the filter:</p>
  
  <pre><code>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) &lt;&gt; 999 Then
              lo.Range.AutoFilter Field:=r, Criteria1:=filter, Operator:=xlFilterValues
          End If
      Next
  Next
  
  End Sub
  </code></pre>
  
  <p>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.</p>
  
  <p>Let me know if you have any ideas. Thanks!</p>
  
  <p>Here is the other related code:</p>
  
  <pre><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 "&lt;&gt;" and "&lt;900" to the criteria list to hide blank rows
      'ReDim Preserve arFilter(UBound(arFilter) + 2)
      'arFilter(UBound(arFilter) - 1) = "&lt;&gt;"
      'arFilter(UBound(arFilter)) = "&lt;900"
  
      'Return the filter array
      BuildFilter = arFilter
  End Function
  </code></pre>