VBA Filter codes needs amendment to filter values which are seperated with a comma
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 30

Thread: VBA Filter codes needs amendment to filter values which are seperated with a comma
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Jul 2010
    Posts
    705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    Hi Fluff,

    I looked into that but that’s not what I want.
    The code which Akuini provided in post # 2 allows me to enter as many criterias as I want along with names separated by comma. Only thing I want fro the code is to allow me to enter part of names instead of full name.

    I experimented a bit myself with this line of the code..... u can see post # 7.

    This allowed me to enter as many names in the cell with comma. But full names
    Split(Range("D4"), ",")


    I amended it a bit to accept contains part instead of full names.
    Split("*" & Range("D4") & "*", ",")

    but its behaving a bit weird.

    Examples

    1) Enter a single criteria... no issues. "To" or "on" or "ny"... all is fine for Tony
    2) Enter multiple criteria.... Like for Tony & Smith. Then I will have to enter it this way for perfect filter. "ny, Sm". Last part of the 1st criteria & First part of the 2nd criteria. Otherwise it will not filter...

    Any idea how to make further changes to make it work in a manner so that it can filter if any part of the name is entered ??
    Last edited by hrayani; Jan 29th, 2019 at 12:22 PM.
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    If you enter the value in D4 like *To*,*Sm* then it will work, but only for TWO values.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Jul 2010
    Posts
    705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    Code:
    If you enter the value in D4 like *To*,*Sm* then it will work, but only for TWO values.
    Exactly that is why I want this part of the code to be modified a bit further - which I am unable to do.

    Code:
    Split("*" & Range("D4") & "*", ",")
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    Unless you use an EXACT match, you can only have TWO criteria when using autofilter.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    Board Regular
    Join Date
    Jul 2010
    Posts
    705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    Ok got it...

    one last issue as of now
    when I press delete in any of the 3 empty cell then it shows filter arrows

    here is the code

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D4, E4, F4")) Is Nothing Then

    Application.ScreenUpdating = False

    arr1 = Split(Range("D4"), ",")
    arr2 = Split(Range("E4"), ",")
    arr3 = Split(Range("F4"), ",")

    With ActiveSheet.Range("D5:F1000")
    .AutoFilter
    On Error Resume Next
    .AutoFilter Field:=1, Criteria1:=arr1, Operator:=xlFilterValues, visibledropdown:=False
    .AutoFilter Field:=2, Criteria1:=arr2, Operator:=xlFilterValues, visibledropdown:=False
    .AutoFilter Field:=3, Criteria1:=arr3, Operator:=xlFilterValues, visibledropdown:=False
    On Error GoTo 0
    End With

    Application.ScreenUpdating = True

    End If

    End Sub
    Last edited by hrayani; Jan 29th, 2019 at 01:52 PM.
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,732
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    Add this before the With statement & do the same for the other arrays
    Code:
    If UBound(arr1) < 1 Then arr1 = "*"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,901
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    I think I can write a code that meet your requirement i.e searching partial match, multi criteria (as many as you want), without showing filter dropdown. But it won't be using autofiler, just a code to show only the rows that meet the criteria.
    Let me now know if your interested.

  8. #18
    Board Regular
    Join Date
    Jul 2010
    Posts
    705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    Code:
    Add this before the With statement & do the same for the other arraysCode:
    
    If UBound(arr1) < 1 Then arr1 = "*"
    Code is not filtering records when i added this part.. Or maybe i am not adding it in the right area

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("D4, E4, F4")) Is Nothing Then
    
    
    Application.ScreenUpdating = False
    
    
    arr1 = Split(Range("D4"), ",")
    arr2 = Split(Range("E4"), ",")
    arr3 = Split(Range("F4"), ",")
    
    
       If UBound(arr1) < 1 Then arr1 = "*"
       If UBound(arr2) < 1 Then arr2 = "*"
       If UBound(arr3) < 1 Then arr3 = "*"
        
        With ActiveSheet.Range("D5:F1000")
        .AutoFilter
        On Error Resume Next
        .AutoFilter Field:=1, Criteria1:=arr1, Operator:=xlFilterValues, visibledropdown:=False
        .AutoFilter Field:=2, Criteria1:=arr2, Operator:=xlFilterValues, visibledropdown:=False
        .AutoFilter Field:=3, Criteria1:=arr3, Operator:=xlFilterValues, visibledropdown:=False
        On Error GoTo 0
        
        End With
        
        
        
    Application.ScreenUpdating = True
    
    
    End If
    
    
    End Sub
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  9. #19
    Board Regular
    Join Date
    Jul 2010
    Posts
    705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    I think I can write a code that meet your requirement i.e searching partial match, multi criteria (as many as you want), without showing filter dropdown. But it won't be using autofiler, just a code to show only the rows that meet the criteria.
    Let me now know if your interested.

    Thanks Akuini,

    Sure please if u can provide a code.

    Row # 2 is where I will be entering the criterias with comma separated values or text.
    Row # 3 contains Headings
    Row # 4 is where the data starts all the way down to some 700 rows & will keep on increasing with time.

    Data goes from column A:V ( Total 22 Coulms).
    Some columns contains text and some contains number and some dates.

    Regards,

    Humayun
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  10. #20
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,901
    Post Thanks / Like
    Mentioned
    31 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Filter codes needs amendment to filter values which are seperated with a comma

    Quote Originally Posted by hrayani View Post
    Thanks Akuini,

    Sure please if u can provide a code.
    Ok, try this:
    Note:
    - Since it's not a real filter then the data is treated as text, so you can't use operator like ">100".
    - You can modify the code to suit your data set up in this part:
    Private Const SRA As String = "A2:E2" 'address where you type the search criteria
    Private Const dS As Long = 2 'row where you type the search criteria
    Private Const dc As Long = 1 'First column of data
    Private Const dr As Long = 4 'First row of data (exclude header)


    Code:
    Private Const  SRA As String =  "A2:E2" 'address  where you type  the search criteria
    Private  Const dS As  Long = 2   'row where you type the search criteria
    Private  Const dc As  Long = 1   'First column of data
    Private  Const dr As  Long = 4   'First row of data (exclude header)
    
    Private  Sub  Worksheet_Change(ByVal Target  As Range)
    Dim i  As Long, j  As Long, n  As Long
    Dim  m As Long, p  As Long
    Dim r As Range
    Dim arr, z, x
    
    If  Not Intersect(Target, Range(SRA))  Is Nothing  Then
        n =  Range(SRA).Resize(100000).Find("*",  SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.ScreenUpdating = False
         Range("A" & dr & ":A"  & n).EntireRow.Hidden = False
    
    If WorksheetFunction.CountA(Range(SRA)) > 0 Then
            For Each r In Range(SRA)
            j = r.Column
                If Len(Cells(dS, j)) > 0 Then
                    arr = Split(Cells(dS, j), ",")
                    For i = dr To n
                       z = Cells(i, j)
                        If z = ""  Then Rows(i).EntireRow.Hidden =  True
                       If Rows(i).RowHeight > 0 Then
                               m = 0
                           For Each x In arr
                                m = m + InStr(1, z, x, 1)
                                 If m > 0  Then Exit  For
                           Next
                                If m = 0  Then Rows(i).EntireRow.Hidden =  True
                       End If
                    Next
                End If
            Next
        End If
        
        Application.ScreenUpdating = True
    
    End If
    
    On Error Resume Next
    p = Range("A" & dr & ":A" & n).SpecialCells(xlCellTypeVisible).Cells.Count
    Application.StatusBar = "Found " & p & " rows"
    On Error GoTo 0
    
    End Sub
    
    
    Sub toClearFilter()
    rr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A1:A" & rr).EntireRow.Hidden = False
    Range(SRA).ClearContents
    
    End Sub
    
    Example:

    ABCDE
    1
    2a,ean,o3
    3NAMECITYSTATEIDDATE
    20eeAltamonte SpringsFlorida23402-Feb
    24TrumanAnaheimCalifornia23402-Mei
    27LeonardoAnkenyIowa93301-Apr
    28MarlonatAnn ArborMichigan23403-Mei
    43LeonardoAtlantaGeorgia93303-Mei
    45aAttleboroMassachusetts34502-Feb

    Sheet1





    Workbook:

    https://www.dropbox.com/s/gvvpo7240k...omma.xlsm?dl=0

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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