Excel VBA filtering loop issue

[dotsent]

Hi guys! Trying to implement a VBA loop the first time and struggling with it. I have table Table1; separately defined range Filtered (same area as Table1, however no headers) and hardcoded array of values Arr. I want VBA to cycle through all those values in array and use each of them to filter the 1st column of Table1. If filtering provides results (visible rows - other than table header), I want to run a separate code, but if there is no match and the range is filtered blank, I want to just go to next value in array and continue with the loop.

I was testing with a simple nested code that fills in A2 values top to bottom on every positive match for testing purposes. I see how VBA throttles through the filtering with the values in array, however I don't get expected results from the nested code, so I assume the IF clause is not working properly.

If Filtered.Rows.Count > 1 And Not Filtered Is Nothing Then

- this provides a positive response on all runs, even if all values from array are not available in table.

If Filtered.Rows.Count > 0 And Not Filtered Is Nothing Then

- this would provide all negative runs

I have designed array values and table contents to only partly match.

Sub Filter_item()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim Filtered As Range, i As Long, Arr

Arr = Array("Apple", "Orange", "Grape")

For i = LBound(Arr) To UBound(Arr)
    With ws.ListObjects("Table1").Range
        .AutoFilter Field:=1, Criteria1:=Arr
        Set Filtered = .SpecialCells(xlCellTypeVisible)

    End With

  If Filtered.Rows.Count > 1 And Not Filtered Is Nothing Then
 
  For Each cell In Range("Testrange").Cells
  If IsEmpty(cell) = True Then cell.Select: Exit For
  Next cell
        
  ActiveCell = Range("A2").Value
 
  End If

  Set Filtered = Nothing

Next i

End Sub

What could I be missing here? Thanks a lot for any input?

 

[Fluff]

Firstly, this line doesn't work

.AutoFilter Field:=1, Criteria1:=Arr

I suspect it should be

.AutoFilter Field:=1, Criteria1:=Arr[COLOR=#ff0000](i)[/COLOR]

Secondly, this line

Filtered.Rows.Count

Will only return a value greater than 1 if the 1st row of data is visible

 

[dotsent]

Interesting, I thought the filtering loop to be OK as the VBA looked to quickly push through different filtering options and always ended with the final value within the array being used as a filter. So I thought Arr part would work, but gave it a try and thought the code ran the same with Arr(i).

How could I adjust for IF to check for empty range in "Filtered"?

 

[Fluff]

If you step through the code using F8 & look at the sheet being filtered, is it getting filtered correctly, on each value in the array?

 

[dotsent]

Didn't know the F8 trick before! But went though it step by step and it is indeed launching the filtering in 3 different cycles and with details as I expected. However with one value, filtering result was a blank range (excl headers) but it was still treated the same way by the IF cycle (expected the extra code NOT to be launched for it). So I'm somewhat confident the array and filtering loop should be OK.

 

[dotsent]

If you step through the code using F8 & look at the sheet being filtered, is it getting filtered correctly, on each value in the array?

I have to correct myself as it was indeed necessary to use Arr(i) for filtering through the array. I was also able to fix the IF clause and link it to SUBTOTAL formula which counts visible rows in a range. Thanks a lot for the valuable help!

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[Waimea]

Hi dotsent,

could you post your working code?

 

[dotsent]

Sure - this appears to be working for me:

Sub Filter_mail()


Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)
Dim i As Long, Arr


Arr = Array("value1", "value2", "value3", "value4")


For i = LBound(Arr) To UBound(Arr)
    With ws.ListObjects("Table1").Range
        .AutoFilter Field:=1, Criteria1:=Arr(i)
       
    End With         
          
    If Range("Check") <> 0 Then
          
    Call email
    
    Application.Wait (Now + TimeValue("0:00:15"))
  
    End If


Next i


End Sub

A few comments if someone is wondering. Excel range "Check" has a SUBTOTAL formula with COUNTA subfunction to check if the table range has any visible rows (e.g. filtering has provided positive results). So no results = value 0

I have read it's not encouraged to use call function in VBA code, however I run an e-mailing code (originally by Ron de Bruin) and instead of sending plain text email, I want to include table range in HTML-format which works by running another sub-function. I wasn't able to run it straight inside the IF nest (probably possible though), but launching it over call-function, worked fine.

I also added the Application.Wait - there will be ~15 values in array and ~10 emails to be sent, however as the code will send this over to Outlook to manage, I wanted to add this pause in order not to risk overloading Outlook with a several emails within a very short period of time. Likely won't be a problem regardless, but more of a precaution.