0

I have gone down a rabbit hole with this and am probably going to be told I've gone about it the wrong way, but anyway:

I have a set of buttons that I want users to be able to press to automatically filter the dataset. The buttons should filter each column but each button should filter on top of whatever filters are already in place. I could use the normal auto-filter; however it appears I am limited (to 2?) text options. On top of this, i would ideally like the user to be able to write in their own text to be filtered in the future.

I have therefore built some dynamic named ranges in a separate sheet, that will expand automatically when the user types in additional text (i.e. text phrases that will be called upon for the filter). In the example below I have called one of these lists: "List_of_text_to_be_filtered_1"

In terms of which columns are being filtered at any one time, I have made another dynamic named range (to expand the range automatically to the end row), called "ColumnA":


Option Explicit
Sub Sort1()

With ActiveSheet
    
     .Range("columnA").AdvancedFilter _
                        Action:=xlFilterInPlace, _
                        CriteriaRange:=Range("List_of_text_to_be_filtered_1")
End With
End Sub

The problem that I am encountering is that I have another set of code that basically identical, e.g.


Option Explicit
Sub Sort1()

With ActiveSheet
    
     .Range("columnB").AdvancedFilter _
                        Action:=xlFilterInPlace, _
                        CriteriaRange:=Range("List_of_text_to_be_filtered_2")
End With
End Sub

But when the user runs this, the advanced filter gets reset and doesn't build on the last filter. Is there a way to build up on the last advanced filter?

Let me know if you want to see my dynamic named ranges formulas.

Thanks.

Edit:

I've changed it to auto-filter with an array but something still isn't working (it appears to be choosing every item on the list, yet nothing is being displayed):

Sub CellsToArray()
    
    Dim x           As Integer
    Dim myArray     As Variant, lastrow As Long
    
    With Range("Names_backup")
        intFirstCol = .Column
        intlastcol = intFirstCol + .Columns.Count - 1
    End With
    
    With shLists
        lastrow = .Cells(.Rows.Count, ("T")).End(xlUp).Row
    End With
    
    For x = Range("Names_backup").Row To lastrow
        
        myArray = Cells(x, intlastcol).Value
        
        x = x + 1
        
    Next x
    
    With Range("ColumnA")
        intFirstCol = .Column
        intlastcol = intFirstCol + .Columns.Count - 1
    End With
    Range("ColumnA").AutoFilter Field:=intlastcol, Criteria1:=Array(myArray), Operator:=xlFilterValues
    
End Sub

Where Names_Backup is the cell header containing the list of text words that should be used to filter. And "ColumnA" is the named range of the column I want to be filtered.

Boswell
  • 102
  • 2
  • 16
  • 1
    I am afraid that `AdvancedFilter` cannot work as you want, using that code. You can do it only if you copy the previous filtered range to another location and apply `AdvancedFilter` on this new range... I mean, using `xlFilterCopy` instead of `xlFilterInPlace` and specify where to copy the filtering result. And the following filter to be applied on the previously returned filtered range. – FaneDuru Aug 27 '21 at 11:14
  • ah that's a shame. Can you suggest an alterative way to filter multiple columns with text that has been written within an excel sheet as i've described? – Boswell Aug 27 '21 at 11:20
  • This is the way Excel works... But `AutoFilter` can be used for another column, keeping the filtered range of the previous filter. I mean, consecutive filtering. It is able to also use multiple strings in filtering, placing them in arrays. But more than two such criteria only in case of equality. I mean not for "Not equal to" ("<>"), which accepts maximum two strings, if I remember well. – FaneDuru Aug 27 '21 at 11:25
  • 1
    Use an array of filter values e.g. https://stackoverflow.com/questions/66385694/filter-the-data-by-matching-strings-from-the-range/66386037#66386037 – Absinthe Aug 27 '21 at 11:31
  • @Absinthe I have created the array as you have suggested but something still isn't working - would you mind having a look at my edited question please? – Boswell Aug 27 '21 at 13:00
  • Hi, that's not how array elements are assigned. Google for VBA arrays. – Absinthe Aug 27 '21 at 14:09
  • @Absinthe I will try; although googling VBA Arrays feels quite open ended! – Boswell Aug 27 '21 at 14:23
  • `myArray` should be 1D array and it should be used as `Criteria1:=myArray`. `myArray` is/should be already an array... – FaneDuru Aug 27 '21 at 20:00
  • You might want to start by using a search for vba + arrays + "chip pearson" . Also, maybe brave the search bar above and use: https://stackoverflow.com/search?q=VBA+AUTOFILTER+ARRAY – QHarr Aug 28 '21 at 05:31
  • An array is a variable that holds many values, to assign or read those values you use an index number. Think of an array like a list of values, no. 3 in the list = whatever, no. 5 in the list = something else. They can get more complicated with multidimensional arrays (no. 3, part 2 = this) but you dont need that. So, this `myArray = Cells(x, intlastcol).Value` won't work because you're not specifying the place in the list, you want `myArray(x) = Cells(x, intlastcol).Value`. But, you either need to `Dim` the array with the number of values it will hold, or `Redim` the array before each next x. – Absinthe Aug 31 '21 at 10:52
  • Again, read up because fully explaining arrays is beyond the scope of this question and there are dozens of tutorials out there. – Absinthe Aug 31 '21 at 10:54
  • Thank you @Absinthe - upon reading into it I realised I can't do what I want to do even with an array - the auto-text won't work for me because i wanted to use more than 2 wildcard's (*). I therefore have decided to change my ideas for what I am working on and instead use check boxes with true/false. It will be a lot simpler in the long run. Thank you for your help anyway. – Boswell Aug 31 '21 at 14:21

0 Answers0