Filter function returning zero rows. Assigning results to an array.

[HighAndWilder]

Sample code which demonstrates a problem that I am having.

Filter by month on separate tab Version 4.xlsm
	H	I	J	K	L	M
1		C9	C10	C11	C12
2		10	5	3	9
3		8	3	4	5
4		9	6	4	1
5		9	6	2	1
6		5	3	6	9
7		3	8	4	5
8
Dashboard2

Cell Formulas
Range	Formula
I1:L1	I1	="C" & COLUMN()

Public Sub subFilterData()
Dim arr() As Variant
Dim strFormula As String
Dim intRows As Integer
  
  ActiveWorkbook.Save
  
  ' Works fine, one row filtered.
  strFormula = "FILTER(I2:L7,I2:I7=10,"""")"
    
  ' intRow is assigned a value of 1 which is correct.
  intRows = Evaluate("Rows(" & strFormula & ")")
  
  ' Assign the filter result to an array and a four row one dimensional array is created.
  ' If more than one row is filtered then a two dimensional array is created.
  arr = Evaluate(strFormula)
    
  ' If no rows are filtered.
   strFormula = "FILTER(I2:L7,I2:I7=100,"""")"
  
  ' intRow is assigned a value of 1 which is INCORRECT.
  ' This suggests that executing the next line would be fine.
  intRows = Evaluate("Rows(" & strFormula & ")")
    
  ' Assign the result to an array and an error 13 - Type mismatch is returned.
  arr = Evaluate(strFormula)
  
  ' How does one test to see if the filter returns zero or one row accurately?
  
End Sub

 

[Kaper]

Check the below code. If it's working as you expect, you may create a function out of it. or simply add similar check to your real code.

Public Sub subFilterData()
Dim arr() As Variant
Dim strFormula As String
Dim intRows As Integer
Dim tmp As Variant

'  ActiveWorkbook.Save
  
  ' Works fine, one row filtered.
  strFormula = "FILTER(I2:L7,I2:I7=10,"""")"
  
  tmp = Evaluate(strFormula)
  If VarType(tmp) >= vbArray Then
    
    ' intRow is assigned a value of 1 which is correct.
    intRows = Evaluate("Rows(" & strFormula & ")")

    ' Assign the filter result to an array and a four row one dimensional array is created.
    ' If more than one row is filtered then a two dimensional array is created.
    arr = Evaluate(strFormula)
  Else
    intRows = 0
    MsgBox "Filter returned no data", vbInformation
  End If

  ' If no rows are filtered.
   strFormula = "FILTER(I2:L7,I2:I7=100,"""")"
  
  tmp = Evaluate(strFormula)
  If VarType(tmp) >= vbArray Then
    intRows = Evaluate("Rows(" & strFormula & ")")
    arr = Evaluate(strFormula)
  Else
    intRows = 0
    MsgBox "Filter returned no data", vbInformation
  End If
  
End Sub

 

[HighAndWilder]

Check the below code. If it's working as you expect, you may create a function out of it. or simply add similar check to your real code.

Public Sub subFilterData()
Dim arr() As Variant
Dim strFormula As String
Dim intRows As Integer
Dim tmp As Variant

'  ActiveWorkbook.Save
 
  ' Works fine, one row filtered.
  strFormula = "FILTER(I2:L7,I2:I7=10,"""")"
 
  tmp = Evaluate(strFormula)
  If VarType(tmp) >= vbArray Then
   
    ' intRow is assigned a value of 1 which is correct.
    intRows = Evaluate("Rows(" & strFormula & ")")

    ' Assign the filter result to an array and a four row one dimensional array is created.
    ' If more than one row is filtered then a two dimensional array is created.
    arr = Evaluate(strFormula)
  Else
    intRows = 0
    MsgBox "Filter returned no data", vbInformation
  End If

  ' If no rows are filtered.
   strFormula = "FILTER(I2:L7,I2:I7=100,"""")"
 
  tmp = Evaluate(strFormula)
  If VarType(tmp) >= vbArray Then
    intRows = Evaluate("Rows(" & strFormula & ")")
    arr = Evaluate(strFormula)
  Else
    intRows = 0
    MsgBox "Filter returned no data", vbInformation
  End If
 
End Sub

Thanks Kaper

It is the VarType that is the key.

I will create a function as I'll use it quite a lot in the future.

 

[Kaper]

Glad we could help and thanks for marking answer as a solution

 

[RoryA]

FWIW, you could also just declare arr as Variant and then test it with IsArray.