Results 1 to 15 of 15

listbox array filter has blanks

Threaded View

  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
    35

    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

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