Page 1 of 3 123 LastLast
Results 1 to 10 of 30

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

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

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

    Hi,

    I am using this VBA code to filter records.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range) 
     Application.ScreenUpdating = False
        
        With ActiveSheet.Range("D5:F1000")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="*" & Range("D4") & "*", visibledropdown:=False
        .AutoFilter Field:=2, Criteria1:="*" & Range("E4") & "*", visibledropdown:=False
        .AutoFilter Field:=3, Criteria1:="*" & Range("F4") & "*", visibledropdown:=False
        End With
        
        Application.ScreenUpdating = True
    
    
    End Sub

    Here is what i require:

    If I enter (Tony, Smith) in cell D4 then the code should filter both records for tony & smith.

    As of now the code only allows me to enter a single criteria in cells D4:F4

    I would want the code to allow me to enter multiple criteria entered with a comma sign.

    Any help would be appreciated

    Regards,

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

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

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

    This is untested:
    Make sure you type just comma (without space) between the items e.g "Tony,Smith".
    Code:
    Application.ScreenUpdating = False
        arr = Split(Range("D4"), ",")
        With ActiveSheet.Range("D5:F1000")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues, visibledropdown:=False
        End With
        
    Application.ScreenUpdating = True
    
    Note:
    You use Private Sub Worksheet_Change
    I think you should limit the event to cell D4, otherwise it will be triggered on any cell's change.
    Last edited by Akuini; Jan 29th, 2019 at 08:03 AM.

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    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

    Thanks Akuini,

    Code you provided is working perfect. Can u amend it so that it can work for all columns D, E & F..

    Make sure you type just comma (without space) between the items e.g "Tony,Smith"
    Well i tried with space and its working fine.
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 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,

    Code you provided is working perfect. Can u amend it so that it can work for all columns D, E & F..
    Try this:

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

  5. #5
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    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

    Working Perfect...

    few minor issues..

    1) The code i was using was working with the contains part of filter.. Like Ton for Tony, Sm for Smith. I did not had to enter full name.
    2) When i press delete on a blank cell then all the cells shows filter arrows.

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

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

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

    1) The code i was using was working with the contains part of filter.. Like Ton for Tony, Sm for Smith. I did not had to enter full name.
    Sorry, don't know how to do that, but you can use * like sm* or *mi* & it's case insensitive.


    2) When i press delete on a blank cell then all the cells shows filter arrows.
    You use Private Sub Worksheet_Change
    I think you should limit the event to cell D4,E4,F4, otherwise it will be triggered on any cell's change.

  7. #7
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    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

    Sorry, don't know how to do that, but you can use * like sm* or *mi* & it's case insensitive.
    I tried to experiment a bit myself. Came up with this

    Code:
    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 ??

    Code:
    Split("*" & Range("D4") & "*", ",")


    You use Private Sub Worksheet_Change
    I think you should limit the event to cell D4,E4,F4, otherwise it will be triggered on any cell's change.


    Yes i did that.

    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


    But even if i press delete on any of these 3 cells then filter arrow shows on these 3 columns.
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    How many criteria are you likely to put in one of those cells?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Jul 2010
    Posts
    721
    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,

    How many criteria are you likely to put in one of those cells?
    Not sure at this point of time. May be 8 I guess.

    Actually as of now I have like 15 different names in there which will grow upto 25 in future I reckon.
    So if the code allows me to enter as many as I want then I will also be able to use the filter if I want all the records excluding one or two names - like what we do in filter (does not contain)

    Regards,

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

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    In that case you will need to look at advanced filters. https://www.contextures.com/xladvfilter01.html

    Autofilters can only filter on 2 values if you want to use "contains"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •