Array AutoFilter VBA

gliffix101 · Oct 3rd 2016

Hello All,

This is my first post on the site so please forgive any posting errors. I am working on a macro that creates and adds data to an array. The array then filters based on the results. The issue is that the array runs but only filters based off of the last field in the array. Please note the following:

* Column R contains True/False indicators. The true/false corresponds with a value in Column S.
* The IF statement cycles through and for every "True" found in Column R, it adds the value in Column S to the array - titled arrResults()
* If there are three values marked true, all three are added to arrResults() - as confirmed in the immediate window and the debug statement found in the code
* When the filter happens on Column 13, it only filters based only on the last field (3rd True).

Please take a look at the code and let me know any suggestions. I just need to know why AutoFilter only recognizes the last field in arrResults and does not recognize any of the others.

Thanks,

Bill

Code

Option Explicit
Sub Add_Sheet_Update()
Dim LastRow As Long
Dim Rng As Range, str1 As String, str2 As String
Dim i As Long, wsName As String, temp As String
Dim arrResults()
With Sheets("All Call Center Detail")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = .Range("A1:BT" & LastRow)
End With
With Sheets("Search Form")
    str1 = .Range("E9").Text
    str2 = .Range("E13").Text
End With
Dim x As Integer, y As Integer
With Range("R1:S99") ' 2 columns, any # of rows
    For x = 1 To .Rows.Count
        If .Cells(x, 1) Then
            y = y + 1
            ReDim Preserve arrResults(1 To y)
            arrResults(y) = .Cells(x, 2)
            End If
    Next x
End With
Sheets.Add After:=Sheets("Search Form")
ActiveSheet.Name = ("Results")
    
Sheets("All Call Center Detail").Select
If y > 0 Then Rng.AutoFilter Field:=13, Criteria1:=arrResults
If Not str1 = "" Then Rng.AutoFilter Field:=6, Criteria1:=str1
If Not str2 = "" Then Rng.AutoFilter Field:=7, Criteria1:=str2


Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1")
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Sheets("Results").Activate
ActiveSheet.Columns.AutoFit
wsName = Format(Date, "mmddyy")
If WorksheetExists(wsName) Then
    temp = Left(wsName, 6)
    i = 1
    wsName = temp & "_" & i
    Do While WorksheetExists(wsName)
        i = i + 1
        wsName = temp & "_" & i
    Loop
End If
ActiveSheet.Name = wsName
Range("A1").Select
End Sub

Display More

bryce · Oct 3rd 2016

Re: Array AutoFilter VBA

You aren't looping through the array, you only reference it once in the if statement

Code

If y > 0 Then Rng.AutoFilter Field:=13, Criteria1:=arrResult

you would need to loop through your array ie something like this

Code

for x = LBound(arrResult) to UBound(arrResult)
'do something
next x

it also looks like you could do your filtering and the rest in this part of the code. Rather than build an array, just do the filter activity whenever If .cells evaluates to true

Code

For x = 1 To .Rows.Count 
            If .Cells(x, 1) Then 
                   'DO YOUR FILTER STUFF HERE
            End If 
        Next x

gliffix101 · Oct 3rd 2016

Re: Array AutoFilter VBA

Hi Bryce,

I'm not sure I follow. The array is contained within the initial IF statement that builds it out:

Code

With Range("R1:S99") ' 2 columns, any # of rows
    For x = 1 To .Rows.Count
        If .Cells(x, 1) Then
            y = y + 1
            ReDim Preserve arrResults(1 To y)
            arrResults(y) = .Cells(x, 2)
            End If
    Next x
End With

Each value returned would be added to arrResults. This is why the next section only filters based on arrResults as it's expected there would be multiple filter criteria:

Code

Sheets("All Call Center Detail").Select
If y > 0 Then Rng.AutoFilter Field:=13, Criteria1:=arrResults
If Not str1 = "" Then Rng.AutoFilter Field:=6, Criteria1:=str1
If Not str2 = "" Then Rng.AutoFilter Field:=7, Criteria1:=str2

You mentioned looping through the array. Are you saying that I would need to loop through the code line multiple times for each filter? I thought the purpose of the array was to be able to filter a column on multiple criteria (as if I manually went in and checked the 3 fields I want to filter by)

Bill

bryce · Oct 3rd 2016

Re: Array AutoFilter VBA

That is not how you loop through an array. You are simply calling the array once and then performing actions on that one instance. You are trying to build an array, then filter your sheet for each item in the array right? To do that, you need to loop through it.

On a side note, rather than build an array, simply do your filtering as soon as you identify an item that you otherwise would have placed in an array.

more like this

Code

for x = LBound(arrResults) to UBound(arrResults)


    Rng.AutoFilter Field:=13, Criteria1:=arrResults(x) <----This will cause your filter to loop though your array 
    If Not str1 = "" Then Rng.AutoFilter Field:=6, Criteria1:=str1 
    If Not str2 = "" Then Rng.AutoFilter Field:=7, Criteria1:=str2 
     
    Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1") 
    Application.CutCopyMode = False 
    ActiveSheet.ShowAllData 
    Sheets("Results").Activate 
    ActiveSheet.Columns.AutoFit 
    wsName = Format(Date, "mmddyy") 
    If WorksheetExists(wsName) Then 
        temp = Left(wsName, 6) 
        i = 1 
        wsName = temp & "_" & i 
        Do While WorksheetExists(wsName) 
            i = i + 1 
            wsName = temp & "_" & i 
        Loop 
    End If 
    ActiveSheet.Name = wsName 
Next x

Display More

gliffix101 · Oct 3rd 2016

Re: Array AutoFilter VBA

Hi Bryce,

I played around with the code a bit. This is the closest I've ever been. I'm just having one issue. If I filter on two data points, and let's say the first filter is 8 rows and the second filter is 14 rows, when it pastes the second filter in, it overwrites the last row of the first filter. Any ideas?

Code

Option Explicit
Sub Add_Sheet_Update()
Dim LastRow As Long
Dim Rng As Range, str1 As String, str2 As String
Dim i As Long, wsName As String, temp As String
Dim arrResults()
With Sheets("All Call Center Detail")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = .Range("A1:BT" & LastRow)
End With
With Sheets("Search Form")
    str1 = .Range("E9").Text
    str2 = .Range("E13").Text
End With
Dim x As Integer, y As Integer
With Range("R1:S99") ' 2 columns, any # of rows
    For x = 1 To .Rows.Count
        If .Cells(x, 1) Then
            y = y + 1
            ReDim Preserve arrResults(1 To y)
            arrResults(y) = .Cells(x, 2)
            End If
    Next x
End With
Sheets.Add After:=Sheets("Search Form")
ActiveSheet.Name = ("Results")
    
Sheets("All Call Center Detail").Select
For x = LBound(arrResults) To UBound(arrResults)
     
    Rng.AutoFilter Field:=13, Criteria1:=arrResults(x) '<----This will cause your filter To Loop though your array
    If Not str1 = "" Then Rng.AutoFilter Field:=6, Criteria1:=str1
    If Not str2 = "" Then Rng.AutoFilter Field:=7, Criteria1:=str2
     
    Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1")
    Application.CutCopyMode = False
'    ActiveSheet.ShowAllData
    Sheets("Results").Activate
    ActiveSheet.Columns.AutoFit
'    wsName = Format(Date, "mmddyy")
'    If WorksheetExists(wsName) Then
'        temp = Left(wsName, 6)
'        i = 1
'        wsName = temp & "_" & i
'        Do While WorksheetExists(wsName)
'            i = i + 1
'            wsName = temp & "_" & i
'        Loop
'    End If
'    ActiveSheet.Name = wsName
Next x
Range("A1").Select
End Sub

Display More

gliffix101 · Oct 3rd 2016

Re: Array AutoFilter VBA

Hi Bryce, the above is incorrect. It's not overwriting the last row, it's pasting into A1 - so if I have 8 rows, 4 rows, and 2 rows, it's pasting each into cell A1 and overwriting the first few rows of each result.

bryce · Oct 4th 2016

Re: Array AutoFilter VBA

Ok, can you tell us more about what you are trying to do? I know you are building an array and will filter your worksheet with each element in your array.When the worksheet is filtered, what do you want to happen? Are you copying all visible rows, just certain columns etc. And once you have copied or whatever...what happens then?

gliffix101 · Oct 4th 2016

Re: Array AutoFilter VBA

Hi bryce,

I'm looking to have the sheet filter and copy all visible rows to a new sheet. The macro is a form that filters out data in columns 6, 7, and/or 13 based on user inputs. The results then spot out on a newly created sheet that then renames itself based on today's date and adds a 1, 2, 3 etc if the dated sheet already exists.

bryce · Oct 4th 2016

Re: Array AutoFilter VBA

Hey gliffix101 thanks for the info. If the rest of your code works then all you have to change is

this

Code

Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1")

to this.

Code

Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

gliffix101 · Oct 4th 2016

Re: Array AutoFilter VBA

Thanks Bryce! This is 95% where I need to be. The only thing is that it's also copying over the header each time. So if I have three filters, it copies over the header for each one. Is there a line of code that can search for repeats of the header, delete the line, and shift everything up?

Bill

**rory** · Oct 4th 2016

Re: Array AutoFilter VBA

Cross-posted, and solved, here: http://windowssecrets.com/foru…Filter-by-Array-truncates

Please read the forum rules on cross-posting and follow them in future. Thanks.

gliffix101 · Oct 4th 2016

Re: Array AutoFilter VBA

I'll be sure to do that. I must have missed the section about cross posting.

Bryce - I had cross posted and was provided a clean solution very similar to my original code that resolved all of my functionality issues. Code below for reference. Thank you for your help on this because your responses helped me out on this and a few other issues.

Code

Sub Add_Sheet_Update()
Dim LastRow As Long
Dim Rng As Range, str1 As String, str2 As String, strx As String
Dim i As Long, wsName As String, temp As String
Dim arrResults
With Sheets("All Call Center Detail")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = .Range("A1:BT" & LastRow)
End With
With Sheets("Search Form")
    str1 = .Range("E9").Text
    str2 = .Range("E13").Text
End With
Dim x As Integer, y As Integer
With Range("R1:S99") ' 2 columns, any # of rows
    For x = 1 To .Rows.Count
        If .Cells(x, 1) Then
            If strx = "" Then
                strx = .Cells(x, 2)
            Else:
                strx = strx & "," & .Cells(x, 2)
            End If
            y = y + 1
        End If
    Next x
End With
Sheets.Add After:=Sheets("Search Form")
ActiveSheet.Name = ("Results")
arrResults = Split(strx, ",")
Sheets("All Call Center Detail").Select
If y > 0 Then Rng.AutoFilter Field:=13, Criteria1:=(arrResults), Operator:=xlFilterValues
If Not str1 = "" Then Rng.AutoFilter Field:=6, Criteria1:=str1
If Not str2 = "" Then Rng.AutoFilter Field:=7, Criteria1:=str2


Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1")
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Selection.AutoFilter
Sheets("Results").Activate
ActiveSheet.Columns.AutoFit
wsName = Format(Date, "mmddyy")
If WorksheetExists(wsName) Then
    temp = Left(wsName, 6)
    i = 1
    wsName = temp & "_" & i
    Do While WorksheetExists(wsName)
        i = i + 1
        wsName = temp & "_" & i
    Loop
End If
ActiveSheet.Name = wsName
Range("A1").Select
End Sub

Display More

bryce · Oct 4th 2016

Re: Array AutoFilter VBA

Cool glad you go it solved. I am obviously missing something because I still don't see how you are actually looping through the array.

**rory** · Oct 4th 2016

Re: Array AutoFilter VBA

You don't need to loop. You can specify an array as the criteria for an autofilter but you need to include the Operator:=xlFilterValues argument for it to work.

bryce · Oct 4th 2016

Re: Array AutoFilter VBA

Interesting, so using Operator:=xlFilterValues will cause the filter to filter on each value in the array? That will definitley come in handy. Thanks rory

bryce · Oct 4th 2016

Re: Array AutoFilter VBA

Or is it that the filter simultaneously filters for all elements in the array when using Operator:=xlFilterValues?

Array AutoFilter VBA

Participate now!

Share

Users Viewing This Thread