Listbox search and delete

Question

My datasheet:
sheet ("Database") - A1 to L10000 (entries)
Headers of each Column in A1 to L1

I tried to search the sheet with a userform. I used a ComboBox because there are always the same Substances (25 different Substances).

For the output I used a Listbox.

I want to delete the selected output from the search function in my sheet ("database").

For example
Search after "substance 1" [ComboBox]

After the search:

Listbox
Columnhead is empty (How can I show the column head after the search?)
First row: substance 1
Second row : substance 1
...

I select the second row and press the Button "Delete".
Now it deletes a different entry not the one selected.

How do I delete the correct row with the selected information?

VBA Excel Code:

Listbox - Entries of the database sheet

Private Sub Refresh_data()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim lr As Long
lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row

If lr = 1 Then lr = 2 
    With Me.ListBox1
        .ColumnCount = 13
        .ColumnHeads = True
        .RowSource = "Database!A2:L" & lr
    End With
End Sub

Search in the sheet "database"

Private Sub CommandButton7_Click()  'Search Button

Dim sh1 As Worksheet
Dim row As Long

Worksheets("Database").Activate

UserForm2.ListBox1.RowSource = "" 

Set sh1 = ThisWorkbook.Sheets("Database")

For row = 2 To sh1.Cells(Rows.Count, 2).End(xlUp).Row

    If InStr(1, sh1.Cells(row, 2).Value, Me.ComboBox.Value) <> 0 Then

        Me.ListBox1.AddItem sh1.Cells(row, 1).Value                               
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = sh1.Cells(row, 2).Value  
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = sh1.Cells(row, 3).Value  
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = sh1.Cells(row, 4).Value  
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = sh1.Cells(row, 5).Value  
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = sh1.Cells(row, 6).Value 
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = sh1.Cells(row, 7).Value  
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 7) = sh1.Cells(row, 8).Value 
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 8) = sh1.Cells(row, 9).Value  
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 9) = sh1.Cells(row, 10).Value 

    End If

Next Zeile

End Sub

Delete the entry

Private Sub CommandButton6_Click() 'Delete Button

Worksheets("Database").Activate

Dim a As String
Dim i As Long

For i = 0 To ListBox1.ListCount - 1             
    If ListBox1.Selected(i) = True Then         
    Rows(i + 2).EntireRow.Delete            
    End If
Next

Call Refresh_data 

End Sub

Answer 1

Add the row number to the search results. You can only delete from search results not if list box is showing the full data. I coloured the results in yellow to show difference.

Option Explicit

Private Sub Refresh_data()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Database")
    
    Dim lr As Long
    lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).row
    
     If lr = 1 Then lr = 2
     With Me.ListBox1
        .BackColor = vbWhite
        .ColumnWidths = ""
        .ColumnCount = 12
        .ColumnHeads = True
        .RowSource = "Database!A2:L" & lr
     End With
 End Sub

Private Sub CommandButton7_Click()  'Search Button

    Dim sh1 As Worksheet, c As Long, data, s As String
    Dim LastRow As Long, row As Long, i As Long
    
    Set sh1 = ThisWorkbook.Sheets("Database")
    LastRow = sh1.Cells(Rows.Count, 2).End(xlUp).row
    data = sh1.Range("A1:L" & LastRow).Value
    s = Me.ComboBox.Value
    
    With Me.ListBox1
        .RowSource = ""
        .ColumnCount = 10
        .ColumnWidths = "0" ' hide row numbers
        .BackColor = RGB(240, 240, 200)
        
        For row = 2 To LastRow
            If InStr(1, data(row, 2), s) <> 0 Then
                .AddItem row
                i = .ListCount - 1
                For c = 1 To 9
                   .List(i, c) = data(row, c)
                Next
            End If
        Next
        
    End With

End Sub

Private Sub CommandButton6_Click() 'Delete Button
       
    Dim sh1 As Worksheet, i As Long, r As Long
    Set sh1 = ThisWorkbook.Sheets("Database")
    
    With ListBox1
        ' only delete from filtered list
        If .ColumnCount > 10 Then Exit Sub
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                r = .List(i, 0) ' row in col 0
                sh1.Rows(r).EntireRow.Delete
            End If
        Next
    End With
    Call CommandButton7_Click

End Sub