Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Load filtered data to listbox instead of worksheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,509
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Load filtered data to listbox instead of worksheet

    Hello, I want to find out if this code below here can be made to load the data into a listbox instead of pasting it to the sheet. My knowledge is limited with the how. Thanks in advance.


    Code:
    Sub FilterDates()
      Dim rCrit As Range  
      Sheets("HOME").UsedRange.ClearContents
      With Sheets(“DATA”).UsedRange
        Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
        rCrit.Cells(2).Formula = "=SEARCH(TEXT(C2,""dd""),F2)"
        '.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, copytorange:=Sheets("HOME").Range("B1"), Unique:=False
      End With
      rCrit.Cells(2, 1).ClearContents
    End Sub
    There Is Always A Better Way

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,901
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    Amend reference to listbox as required

    Code:
    Sub FilterDates()
        Dim rCrit As Range
        Sheets("HOME").UsedRange.ClearContents
        With Sheets("DATA").UsedRange
            Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
            rCrit.Cells(2).Formula = "=SEARCH(TEXT(C2,""dd""),F2)"
            .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
        End With
        rCrit.Cells(2, 1).ClearContents
    'empty and re-populate Listbox
        With Sheets("SheetName").ListBox1
            Do While .ListCount > 0
                .RemoveItem 0
            Loop
            .List = Sheets("Data").UsedRange.SpecialCells(xlCellTypeVisible).Value
        End With
    End Sub

  3. #3
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,509
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    I did these amendment to fit my work. But it is not getting me the actual result needed. The rows 1 to 6 are all headers . The actual data starts from row 7 (A7). And I am trying to filter all January dates hence that ""01"" . Let me know if I am doing something wrongly.

    Code:
    Sub FilterDates()
        Dim rCrit As Range
        With Sheets("DATA").UsedRange
            Set rCrit = .Offset(, .Columns.Count).Resize(7, 1)
            rCrit.Cells(7).Formula = "=SEARCH(TEXT(""01"",""mm""),A7)"
            .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
        End With
        rCrit.Cells(7, 1).ClearContents
    'empty and re-populate Listbox
        With Sheets("SheetName").ListBox1
            Do While .ListCount > 0
                .RemoveItem 0
            Loop
            .List = Sheets("Data").UsedRange.SpecialCells(xlCellTypeVisible).Value
        End With
    End Sub
    There Is Always A Better Way

  4. #4
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,509
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    Also the listbox is loading the data with different format than how my data looks in the database. I tried using ".Text" instead of ".Value" but didn't work.

    For example my dates are in the form "dd-mm-yy" but it's loading "dd-mmm-yy".
    There Is Always A Better Way

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,901
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    This works with my test data

    Code:
    Sub FilterDates()
        Dim rCrit As Range, rng As Range, r As Long, c As Long, LBox As OLEObject
        Sheets("HOME").UsedRange.ClearContents
        Set rng = Sheets("DATA").UsedRange
        Set LBox = Sheets("SheetName").OLEObjects("ListBox1")
        
        With rng
            Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
            rCrit.Cells(2).Formula = "=SEARCH(TEXT(C2,""dd""),F2)"
            .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
        End With
        rCrit.Cells(2, 1).ClearContents
    'empty and re-populate listbox
        With LBox.Object
            Do While .ListCount > 0
                .RemoveItem 0
            Loop
            Set rng = rng.Offset(6).Resize(rng.Rows.Count - 6, .ColumnCount)        'takes no of columns from ListBox
            Set rng = rng.SpecialCells(xlCellTypeVisible)
            .List = rng.Value
                For r = 0 To .ListCount - 1
                    For c = 0 To .ColumnCount - 1
                      .List(r, c) = rng.Cells(r + 1, c + 1).Text
                    Next c
                Next r
        End With
    End Sub
    Last edited by Yongle; Feb 3rd, 2019 at 08:13 AM.

  6. #6
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,509
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    Quote Originally Posted by Yongle View Post
    This works with my test data

    Code:
    Sub FilterDates()
        Dim rCrit As Range, rng As Range, r As Long, c As Long, LBox As OLEObject
        Sheets("HOME").UsedRange.ClearContents
        Set rng = Sheets("DATA").UsedRange
        Set LBox = Sheets("SheetName").OLEObjects("ListBox1")
        
        With rng
            Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
            rCrit.Cells(2).Formula = "=SEARCH(TEXT(C2,""dd""),F2)"
            .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
        End With
        rCrit.Cells(2, 1).ClearContents
    'empty and re-populate listbox
        With LBox.Object
            Do While .ListCount > 0
                .RemoveItem 0
            Loop
            Set rng = rng.Offset(6).Resize(rng.Rows.Count - 6, .ColumnCount)        'takes no of columns from ListBox
            Set rng = rng.SpecialCells(xlCellTypeVisible)
            .List = rng.Value
                For r = 0 To .ListCount - 1
                    For c = 0 To .ColumnCount - 1
                      .List(r, c) = rng.Cells(r + 1, c + 1).Text
                    Next c
                Next r
        End With
    End Sub

    Great! ! ! It's working fine. Actually the listbox is on a userform instead of a worksheet. I have fix that.


    The only issue I have is that the formula I have there is not doing my intention. It's loading all the data into the listbox.

    That code is a code I used for a different thing and I am trying to amend it to do a new thing but I think what I am thinking the code will do is far from what's doing.

    This is how I want to filter the data:

    Code:
    rCrit.Cells(7).Formula = "=SEARCH(TEXT(""01"",""mm""),B7)"
    



    So from this formula I was hoping it will filter all dates in January from column B. I just had the impression that's different from the code is actually doing.

    I wish this can be fixed. I am finding it challenging understanding this filtering thing.
    There Is Always A Better Way

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,901
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    Dates are tricky to filter
    - there are various ways to get there
    - here is one method I use to filter between 2 dates

    In example code below (which does not attempt to tie into your code)
    - a valid date in cell D1 filters all dates in column B to display only those dates in same month and year as D1
    - eg only January 2019 rows are visible if date in D1 is 7 Jan 2019
    - do not amend the format of date text to local (US format works!)
    Code:
    Sub AutoFilter_All_Dates_For_Month()
        Dim Crit1 As String, Crit2 As String, cel As Range
        Set cel = Range("D1")
    
        Crit1 = ">=" & Format(DateSerial(Year(cel), Month(cel), 1), "mm/dd/yyyy")           'first day of month of date in D1
        Crit2 = "<" & Format(DateSerial(Year(cel), Month(cel) + 1, 1), "mm/dd/yyyy")        'last day of month of date in D1
        
        Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Crit1, Operator:=xlAnd, Criteria2:=Crit2
    End Sub
    Last edited by Yongle; Feb 4th, 2019 at 02:50 AM.

  8. #8
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,509
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    Quote Originally Posted by Yongle View Post
    Dates are tricky to filter
    - there are various ways to get there
    - here is one method I use to filter between 2 dates

    In example code below (which does not attempt to tie into your code)
    - a valid date in cell D1 filters all dates in column B to display only those dates in same month and year as D1
    - eg only January 2019 rows are visible if date in D1 is 7 Jan 2019
    - do not amend the format of date text to local (US format works!)
    Code:
    Sub AutoFilter_All_Dates_For_Month()
        Dim Crit1 As String, Crit2 As String, cel As Range
        Set cel = Range("D1")
    
        Crit1 = ">=" & Format(DateSerial(Year(cel), Month(cel), 1), "mm/dd/yyyy")           'first day of month of date in D1
        Crit2 = "<" & Format(DateSerial(Year(cel), Month(cel) + 1, 1), "mm/dd/yyyy")        'last day of month of date in D1
        
        Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Crit1, Operator:=xlAnd, Criteria2:=Crit2
    End Sub

    Cool! !! Very brilliant.

    I love this, I will find a way to use some tricks in switching between years.


    So now how do we get the filtered data into the listbox as before?

    Then remove the filter.

    Regards
    There Is Always A Better Way

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,901
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    how do we .... remove the filter?
    by using range variable rng (previously set earlier in the macro)

    Add this at end of the code
    Code:
    On Error Resume Next
    rng.Parent.ShowAllData

  10. #10
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,509
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load filtered data to listbox instead of worksheet

    Quote Originally Posted by Yongle View Post
    by using range variable rng (previously set earlier in the macro)

    Add this at end of the code
    Code:
    On Error Resume Next
    rng.Parent.ShowAllData

    Cool!


    Thanks for your patience
    There Is Always A Better Way

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
  •