0

I ran into a problem where I search for a bill number in list box via a text box. I find that data in list box. But the problem is every other record before that data is also being displayed to it. In the image....I searched for bill number 100

enter image description here

it searches it and display all the data until it reaches bill number 100. It doesn't display after that bill. Just wanted to remove/hide non-required data in list box when I search.

Code for the search command:-

Private Sub cmd_Search_Click()
    Dim h As Worksheet
  
    Set h = ThisWorkbook.Sheets("Sales")
    Dim lr, i, bill, ex As Integer
    lr = h.Cells(Rows.Count, 3).End(xlUp).Row
    bill = Me.txt_Bill_id.Value
    
    For i = 2 To lr
        ex = h.Cells(i, 3).Value
       
            If ex = bill Then
            With Me.lb_SaleList
                .ColumnCount = 10
                .ColumnHeads = True
                .ColumnWidths = "45,45,45,75,75,55,45,90,50,80"
                .RowSource = "Sales!A2:J" & i
         End With
        Else
            MsgBox "Bill Number not correct"
        End If
    Next i
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
NAITIK JAIN
  • 13
  • 4
  • If I'm reading this correctly, you're just trying to [`Range.AutoFilter`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofilter)? – Cyril Dec 02 '21 at 20:54
  • Yeah! You can say that. Only the searched data should be displayed on list box! – NAITIK JAIN Dec 03 '21 at 03:28
  • Try taking a look at https://stackoverflow.com/questions/11872651/filtering-column-based-on-range-vba/11873370, noting you can have the range `"A:A"` be your table (determined dynamically, as a Table, or a fixed range). – Cyril Dec 03 '21 at 13:14
  • I've got add function for this particular data on different form. So range ain't fixed. – NAITIK JAIN Dec 03 '21 at 15:37

0 Answers0