CODEWITHSUNDEEP
excelvba
JoaMika
JoaMika

Reputation: 1827

Check if Listobject Filter Returns any results

I am trying to check whether a filtered listobject returns no results.

Public Function TestFilterResult() As Boolean
Dim filterArea As Range
Dim cellsCount As Long

Set filterArea = ActiveSheet.ListObjects(1).Range

cellsCount = filterArea.SpecialCells(xlCellTypeVisible).Count

If cellsCount = 0 Then
    TestFilterResult = True
End If

End Function

However, the function incorrectly returns TRUE because it accounts for the header row which is always visible. Is there a way around this or perhaps a different approach altogether in checking this?

Upvotes: 0

Views: 308

Answers (1)

Rafał B.
Rafał B.

Reputation: 404

Encouraged by BigBen's comment I would post another solution. If you're sure each of your table row always contains data you can use excel-built SUBTOTAL function

 Public Function TestFilterResult() As Boolean
   Dim tbl As ListObject
   Dim check As Boolean

   Set tbl = ActiveSheet.ListObjects(1)
   check = Application.Evaluate("SUBTOTAL(103," & tbl.Name & ")") = 0

     If check Then
       TestFilterResult = True
     End If

 End Function

But if your table is static better way will be to add column e.g. "Ordinal number" with function ROWS what will make you sure there are not empty rows in your table and then use "=SUBTOTAL(103,YourTableName)" just in Excel cell, there is no need using UDF and VBA in this case.

Upvotes: 2

Related Questions