Excel Userform to search in textbox and filter in a listbox

Question

Hello I am looking for help, I have one textbox and one listbox in an Excel Userform, it works flawlessly except for one small Detail: as soon as the results appear in the listbox they represent the search within all columns. The first column, however is hidden when I type in the textbox, how can I make sure the column remains visible during search? Thanks in advance

Here is the code:

Private Sub UserForm_Initialize()

End Sub

Private Sub TextBox1_Change()

With Sheets("Sheet1")

lr = .Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To lr - 1)
ReDim sn(1 To lr - 1, 1 To 13)
For i = 1 To UBound(arr)
    arr(i) = .Range("A" & i + 2) & " " & .Range("B" & i + 2) & " " & .Range("C" & i + 2) & " " & .Range("D" & i + 2) & " " & .Range("E" & i + 2) & " " & .Range("F" & i + 2)
    If InStr(1, arr(i), TextBox1) > 0 Then
        j = j + 1
        For X = 2 To 8
            sn(j, X - 1) = .Cells(i + 2, X)
        Next
    End If
Next
ListBox1.List = sn

End With

End Sub 

Answer 1

Consistent Array Approach

Your original code shows a mixture of array and range loops when creating a filtered listbox list. In order to be more consistent here by looping through arrays only *) , you could refine your code as follows (e.g. using the same match check via Instr):

Userform Event procedure TextBox1_Change()

Private Sub TextBox1_Change()
  Const STARTROW = 3
  Dim i&, iCnt&, r&, c&                                                       ' array counters for "rows" and "columns"
  Dim sn, tmp                                                                 ' variant 2-dim 1-based arrays
  With Sheets("Sheet1")
      iCnt = .Range("A" & Rows.Count).End(xlUp).Row - STARTROW + 1            ' items counter
      ReDim sn(1 To iCnt, 1 To 13)                                            ' provide for filtered data array
      For i = 1 To iCnt
         'assign current data row to 2-dim 1-based temporary array
          tmp = .Range("A" & (i + 2) & ":F" & (i + 2))                        ' current data row (c.f. OP)
         'compare search string with concatenated data string from current row
          If InStr(1, concat(tmp), TextBox1.Text) > 0 Then                    ' check occurrence e.g. via Instr
              r = r + 1                                                       ' new rows counter
              For c = 1 To UBound(tmp, 2)                                     ' col counter
                  sn(r, c) = tmp(1, c)                                        ' collect found row data
              Next
          End If
      Next
      ListBox1.List = sn                                                      ' assign array to .List property
  End With

End Sub

Helper function concat() called by above event procedure

Private Function concat(ByVal arr, Optional ByVal delim$ = " ") As String
' Purpose: build string from 2-dim array row, delimited by 2nd argument
' Note:    concatenation via JOIN needs a "flat" 1-dim array via double transposition
  concat = Join(Application.Transpose(Application.Transpose(arr)), delim)
End Function

Notes

*) Looping through a range by VBA is always time consuming, so do this with arrays instead.

You might be also interested in the following solution demonstrating the use of the listbox Column property. By playing around this could help you to remove the superfluous blank rows in the listbox.