+ Reply to Thread
Results 1 to 15 of 15

listbox array filter has blanks

  1. #1
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    listbox array filter has blanks

    Hi
    I have a user form with list box that displays a named range of data. In a combo box I select a string from a drop down list, once selected the list box filters the data on records with the string selected into an array and the array is set to the list box.
    Works fine but I'm getting blanks for the records which don't match. Code for the combo box selection is below, lstGatePrices is the list box, gate_information contains the source data which is removed once the button is selected (lstGatePrices.RowSource=""), images pre and post selection are also include, any help appreciated. Basically I want to filter a list from a combo box, I also want to filter further on more columns but that can come later. Thanks

    Private Sub cmbModel_Change()

    Dim out As String, out2 As String
    Dim iCount As Integer
    Dim arr As Variant
    Dim rg As Range
    Dim i As Long, j As Long, row As Long
    Dim vDataArray(1 To 200, 1 To 6)

    On Error Resume Next

    lstGatePrices.RowSource = ""


    Set rg = Worksheets("gate_information").Range("A1").CurrentRegion

    row = 2
    For i = 1 To rg.Rows.Count
    If rg.Cells(i, 1) = Me.cmbModel.Value Then

    For j = 1 To 6
    vDataArray(i, j) = rg.Cells(i, j)
    out2 = out2 & " " & rg.Cells(i, j)

    Next j


    End If
    Next i

    lstGatePrices.List = vDataArray
    MsgBox "bob " & Me.cmbModel.Value & " " & Me.lstGatePrices.ColumnCount & " " & Me.lstGatePrices.ListCount & " " & out & " out2 " & out2
    End Sub
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: listbox array filter has blanks

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 04-02-2020 at 12:55 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: listbox array filter has blanks

    Although not directly related to your immediate query.
    I saw your original post and started putting ideas together.
    Regrettably events got in the way and I lost track, today's post awakened me again.
    Some of the content may be useful ????
    The UserForms are triggered by the small green button in cell 'A1'
    As I had no clue to prices there is just a simple formula that helped me fill the form for testing only.
    As the data quantity is small I opted for a 'sleeper sheet' for data manipulation rather than array manipulation.
    Rather than me just bin the app you may as well look at it (it is no where the finished article).
    torachan.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: listbox array filter has blanks

    Slow start to the morning for me.
    Second look at your query and I remembered a solution that I recently gave on this forum.
    Very similar search pattern to yours, methods used may of future use.
    torachan.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    Re: listbox array filter has blanks

    Many thanks for the replies, I'm pleased to say it works a treat. My next step is to filter further on height, depth and width starting with height.
    I've added a text box and code to filter on the full string and this filters but it's behaving oddly. For instance when I put 16 in the height box there should be loads of values
    but it's only showing some.
    And ideally I want the contents of the text box aligned with height to filter just on the height value.
    In the code below when the Height text box is changed I set xstr with a comma separator and have tried to split the string so the instr searches just on the third value (height)
    but the form behaves oddly and it doesn't work. Can you assist with this please ? Images attached, thanks

    Private Sub txtHeight_Change()
    Dim out As String, out2 As String
    Dim iCount As Integer
    Dim arr As Variant
    Dim rg As Range
    Dim i As Long, j As Long, row As Long
    Dim vDataArray(1 To 200, 1 To 6)
    Dim hArray() As String

    On Error Resume Next

    lstGatePrices.RowSource = ""

    ystr = Me.cmbModel.Value
    hstr = Me.txtHeight.Value
    Ary = Worksheets("gate_information").Range("GatePrices3")

    nr = 0
    ReDim Nary(1 To UBound(Ary), 1 To 6) As Variant

    For r = 2 To UBound(Ary)
    xstr = Join(Application.Index(Ary, r, 0), ",")
    'hArray = Split(xstr, ",")
    'height = hArray(3)
    'out2 = out2 & " " & height
    'If InStr(1, xstr, ystr, vbTextCompare) And InStr(1, height, hstr, vbTextCompare) Then
    If InStr(1, xstr, ystr, vbTextCompare) And InStr(1, xstr, hstr, vbTextCompare) Then
    nr = nr + 1
    For c = 1 To UBound(Ary, 2)
    Nary(nr, c) = Ary(r, c)
    Next c
    End If
    Next r

    'lstGatePrices.List = vDataArray
    lstGatePrices.List = Nary
    'MsgBox "bob " & Me.cmbModel.Value & " " & Me.txtHeight.Value & " out2 " & out2
    End Sub
    Attached Images Attached Images

  6. #6
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: listbox array filter has blanks

    Please upload a desensitised workbook, its a lot easier to see what is going on.

  7. #7
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    Re: listbox array filter has blanks

    Workbook attached, all help appreciated, thanks
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: listbox array filter has blanks

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    Re: listbox array filter has blanks

    Ha - thank you AlphaFrog, that is perfecto !! And thanks to torachan, what a great source of information and inspiration this site is !

  10. #10
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,544

    Re: listbox array filter has blanks

    @AlphaFrog beat me, must take shorter lunch breaks.

  11. #11
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    Re: listbox array filter has blanks

    Hi again, sorry to bother you further, I've now added another drop down box which selects Type either Standard or NonStandard and combined that with filtering of Model and Height.
    It seems to work okay apart from an odd instance. When I filter Model on LCHP I get the correct details, filtering further on Type I get the correct details again. When I type 5 into the Height field
    I get a reduced number of fields all with a 5 in, all good. But when I enter 4 all the entries disappear but there is a height of 1450. So whether a 4 or 5 is entered anything with 1450 should
    remain - I'm puzzled! Please take a look and opine if possible. Thanks. Latest file and an image of screenshots attached
    Attached Images Attached Images
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: listbox array filter has blanks

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    Re: listbox array filter has blanks

    Thanks AlphaFrog, that now works. I've realised another challenge, if the Model and/or Type values are left blank and a value entered in Height it displays nothing which is right because of the instr clause on Model and Type but ideally the user should be able to enter a height and it display all model/types.
    I could write several if blocks with the code below contained in each block adjusted depending if Model/Type are blank but wondering if there's a better way of doing it. It could also apply to Depth and Width once I've set those up. Any suggestions ? Thanks
    For r = 1 To UBound(Ary)
    xstr = Join(Application.Index(Ary, r, 0), ",")
    If INDEXLIST(xstr, ",", 2) = tstr And InStr(1, Ary(r, 1), mstr, vbTextCompare) > 0 And InStr(1, Ary(r, 3), hstr, vbTextCompare) > 0 Then
    nr = nr + 1
    For c = 1 To UBound(Ary, 2)
    Nary(nr, c) = Ary(r, c)
    out2 = out2 & " " & Nary(nr, c)
    Next c
    End If
    Next r

  14. #14
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: listbox array filter has blanks

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 04-03-2020 at 06:43 AM.

  15. #15
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    Re: listbox array filter has blanks

    Absolutely brilliant! Thank you AlphaFrog

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Toggle Text Filter for blanks and non-blanks with macro
    By PaulOB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-11-2020, 01:59 PM
  2. [SOLVED] ListBox Array creates blank ListBox item
    By onmyway in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-16-2019, 12:33 PM
  3. Filter the array in listbox
    By yinkajewole in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2019, 12:57 PM
  4. Populate (ca 200) Txtboxes depending on listbox selection, live-filter for listbox &1 more
    By InternInNeed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-14-2016, 09:56 AM
  5. Use Selected Items from ListBox As Filter Criteria, Including Blanks
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2015, 06:13 AM
  6. [SOLVED] Filter worksheet based on listbox array
    By MarMo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-08-2013, 08:38 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1