Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2016
    Posts
    3
    Thanks
    0
    Thanked 1 Time in 1 Post

    AutoFilter by Array truncates

    Hello All,

    I am new to the forum and thought I found a post that could help, but no such luck, so I'm adding a new and hoping for the best.

    I have a macro that is scanning two columns (R & S) and for each "True" found in Column R, it adds the corresponding value in Column S to the array called arrResults. Later in the function, it then filters a separate sheet Field 13 by the array. Unfortunately, the autofilter only filters on the last value added to the array. Meaning, if three values are added (verified in the immediate window), the 13th Column is only filtered on the third value. Can someone please let me know what I need to update so that when it filters on the array, it filters for all values within the array and not just the last one?

    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

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    gliffix,

    The modifications in blue should enable your code auto filter the "All Call Center Detail" sheet. The code builds a criteria string using the values in column S whose corresponding value in column R is True. The rest of your code in untouched and presumed problematic free.

    HTH,
    Maud

    Code:
    Option Explicit
    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
    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

  3. #3
    New Lounger
    Join Date
    Oct 2016
    Posts
    3
    Thanks
    0
    Thanked 1 Time in 1 Post
    Maud,

    Three weeks!!! Three weeks I have restructured and rebuilt this thing from every possible freaking angle and like an Angel in the night you come swooping in to save the day. Thank you!!! Thank you so much for posting this. I have ran 10 different form scenarios and this works 10 out of the 10 times.

    Thank you!!!

    Bill

  4. The Following User Says Thank You to gliffix101 For This Useful Post:

    Maudibe (2016-10-04)

  5. #4
    New Lounger
    Join Date
    Oct 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

  6. #5
    New Lounger
    Join Date
    Oct 2016
    Posts
    3
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by YetAnotherOne View Post
    Yes, that was my post as well. I cross posted to get a response, but this one hit the nail on the head with the cleanest code.

    Thank you all for your guidance on this.

    Bill

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •