Excel VBA filtering loop issue

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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.
Code:
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.

Code:
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Firstly, this line doesn't work
Code:
.AutoFilter Field:=1, Criteria1:=Arr
I suspect it should be
Code:
.AutoFilter Field:=1, Criteria1:=Arr[COLOR=#ff0000](i)[/COLOR]
Secondly, this line
Code:
Filtered.Rows.Count
Will only return a value greater than 1 if the 1st row of data is visible
 
Upvote 0
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"?
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0
Hi dotsent,

could you post your working code?
 
Upvote 0
Sure - this appears to be working for me:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top