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

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

  1. #21
    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 Akuini,

    Many Thanks for the code you provided. It made my life much easier

    Two things i would request you to add in the code.

    1) I want row # 2 default height to be 35 if there is no criteria.
    2) If there is any criteria then row height should be autofit.

    Regards,

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

  2. #22
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,917
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    3 Thread(s)

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

    Try this:

    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 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
            Rows(dS).AutoFit
            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
            
        Else
                Rows(dS).RowHeight = 35
        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

  3. #23
    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

    Thanks Akuini,

    Many Many Thanks.... Working Just Perfect

    Regards,

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

  4. #24
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,917
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    3 Thread(s)

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

    You're welcome, glad to help, & thanks for the feedback.

  5. #25
    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 Akuini,

    Just to update you...

    I wanted the criteria row to be auto fit if its less then 35.

    i changed the line in the code accordingly.

    Code:
    If Rows(dS).RowHeight < 35 Then Rows(dS).AutoFit
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  6. #26
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,917
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    3 Thread(s)

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

    Ok, that would work.

  7. #27
    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 Akuini,

    Thanks once again for the code you provided few months back. I am using it and its working just fine. There is an issue I came across and would like to ask you.

    The code was not filtering columns which contains dates (Columns C,N & O) in my case - so I tried to figure out what actually is happening and i came up with this that the cells where i am supposed to enter the criteria is formatted as custom format (mmm-yy) so I changed the format to general and it worked fine I mean it started filtering the dates columns as long as i am entering years in search criteria like 2014, 2019 etc. but as soon as i enter for example Oct-13 then it shows the filter once and the format of the cell again changes to (mmm-yy) and then i am not even able to enter years.

    I would really appreciate your help.

    Regards,

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

  8. #28
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,917
    Post Thanks / Like
    Mentioned
    32 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
    Hi Akuini,

    Thanks once again for the code you provided few months back. I am using it and its working just fine. There is an issue I came across and would like to ask you.

    The code was not filtering columns which contains dates (Columns C,N & O) in my case

    Humayun
    I hope I understand you correctly.
    Try this:
    1. Change this line:
    Code:
    z = Cells(i, j)
    to this:
    Code:
    z = Cells(i, j).Text
    2. Format row 2 as text.

    3. You may use whatever date format you want in data (below row 3) in columns C,N & O or any column, but it will be treated as text, it means "what you see is what you get".
    So for example, if you use "mmm-dd" then you can't search by year because there is no year shows in the cells.

  9. #29
    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 Akuini,

    Thanks for the reply as always

    I made the change in the code as you said.
    Moreover, I also changed row # 2 to text format where i am entering the search criteria.

    If I want to see years then I just type -14 in the criteria and it shows all data of 2014 year
    If I want to see dates then I just type 14- in the criteria and it shows all data of 14 days

    Now I get what I see

    Thanks & Regards,

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

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

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

    You're welcome, glad it works.

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
  •