1

I'm trying to filter my table by looking if a cell contains one of those names, and when I use theFor it shows all data in one row.

Here is the code: 

    Dim tab(3) as string '
    'tab(0) = "*valerie dupond*"'
'tab(1) = "*emanuel babri*"'
'tab(2) = "*raphael gerand*"'

For i = 0 To 2

'Worksheets("Feuil1").Range("A1").AutoFilter field:=2, Criteria1:=tab , ''Operator:=xlFilterValues'

'Next'
sanyassh
  • 8,100
  • 13
  • 36
  • 70
Aya Lahrichi
  • 19
  • 1
  • https://chat.stackoverflow.com/rooms/193554/pies-den @Aya Lahrichi – Pie May 19 '19 at 12:09
  • Can you provide your excel sheet and expected output sheet. No guarantee but i will try to help @Aya Lahrichi – Pie May 22 '19 at 07:08
  • Mutliple wildcards are not allowed in `.AutoFilter` (maximum is 2). Therefore you need to workaround as described in the answer that I linked in the top of your question. – Pᴇʜ May 22 '19 at 07:47
  • Hi I think you need to create an array in the criteria itself. **wb.UsedRange.AutoFilter Field:=bdlgschc, Criteria1:=Array("RMS", "ATC", "RMS IND", "FIRE"), Operator:=xlFilterValues** This line works for me .... If you can try and create a criteria like this it should work. – Mikku May 22 '19 at 08:26
  • I have edited the question and added the code line that works for me. – Mikku May 22 '19 at 08:30
  • @MohitBansal note that she tried to use wildcards `*` and you did not. There are only a maximum of 2 wildcards allowed in `.Autofilter` but she tried using 3. Therefore your approach will not work for her. – Pᴇʜ May 22 '19 at 08:56
  • @Pᴇʜ .... Okay. Thanks for the update :) – Mikku May 22 '19 at 09:20

1 Answers1

0

The problem that you are having is that you can't have wildcards ("/*") when you filter on an array.

​

Getting around that restriction is difficult, but not impossible. The way I've done it before is to do something like this:

1) Copy all of the values in the column you are filtering on (column 2, I think) to a blank sheet.

2) Remove duplicates.

3) Loop through all of the remaining rows and delete any that do not match the criteria.

4) Put the remaining values in an array.

5) Filter the original data on that array.

I don't have access to the code, but it is something like what is below. I didn't test it. I'm not on a computer with Excel right now so you will have to clean it up, fix the errors and also enable Regular Expressions in Visual Basic. Should be in the Tools->References menu. You can also tinker with this code a bit to manipulate it to do it.

Dim i As Integer

Dim c As Integer

Dim lRow As Integer

Dim regEx As New RegExp

Dim rEx As String

Dim arr(1) As String



lRow = Range(shSheet.Rows.Count, ActiveCell.Column).End(xlup).Row 'Get's the last row of the current column selected so make sure to select the column you are trying to filter.



rEx = "^.*(valerie dupond|emanuel babri|raphael gerand).*$" ' The test string for the Regular Expression to match


'Setting up the Regular Expression.

With regEx

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = strPattern

End With



i = 0 'Sets i to be looped through your values.

c = 1 'C will be set to store the values in the array.



'Loops through every row in your table trying to match the pattern above

For i to lRow

If regEx.Test(LCase(ActiveCell.Value)) Then
arr(c) = ActiveCell.Value

c = c + 1

ReDim Preserve arr(c)

End If

ActiveCell.Offset(1,0).Select
Next i



'Sets the filter

Worksheets("Feuil1").Range("A1").AutoFilter field:=2, Criteria1:=arr , ''Operator:=xlFilterValues

Method 2:

Two points:

  1. You don't need the FOR loop. Criteria1=tab will filter for all the criteria, no loop needed
  2. You cannot use wildcards if you are using this array method to search for multiple terms. If you want to use wildcards, you have to use different syntax and are limited to just two terms

code 2

Just remove the wildcards. For example if you need to just match "valerie dupond" but not "Mrs. valerie dupond"

Sub FilterMe()
        Dim names(3) As String
        names(0) = "valerie dupond"
        names(1) = "emanuel babri"
        names(2) = "raphael gerand"
        Worksheets("Feuil1").Range("A1").AutoFilter field:=2, Criteria1:=names, Operator:=xlFilterValues
    End Sub

Again you cannot use autofilter to filter more than two terms with wildcards

Pie
  • 584
  • 1
  • 6
  • 22