RemoveItem in for loop

Ezzor 1 Reputation point
2023-01-05T09:58:06.393+00:00

Im trying to make a search function that can check if input is a number or letter and then remove items that does not fulfill the argument.

Using ".Additem row" works fine, but Using ".RemoveItem row" gives a runtime error, how do i resolve this?

Private Sub TextBox1_Change()

Dim sh1 As Worksheet, c As Long, data, s As String
Dim LastRow As Long, row As Long, i As Long
Dim A, B As Integer

Set sh1 = ThisWorkbook.Sheets("Database")  
LastRow = sh1.Cells(Rows.Count, 2).End(xlUp).row  
data = sh1.Range("A1:I" & LastRow).Value  
s = Me.TextBox1.Value  
A = Len(Me.TextBox1.Text)  
B = Len(Me.TextBox1.Text) - 1  

With Me.ListBox1  
    .RowSource = ""  
    .ColumnCount = 9  
    .ColumnWidths = "0" ' hide row numbers'  
    .ColumnHeads = True  
      
    For row = 2 To LastRow  
        If Me.TextBox1.Text = "" Then  
        Call UserForm_Initialize  
        Exit Sub  
      
        ElseIf IsNumeric(UCase(Mid(Me.TextBox1.Text, A, A))) = False And IsNumeric(UCase(Mid(Sheet1.Cells(row, 2).Text, A, A))) = True Then  

            'Ensure ListBox contains list items'  
            If ListBox1.ListCount >= 1 Then  
            .RemoveItem row  
              
            End If  
        End If  
    Next  
      
End With  
  

End Sub

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,532 questions
0 comments No comments
{count} votes