VBA Excel: Show visible cells in listbox only

Question

I have the below mentioned code, I am trying to load filtered cells only into the listbox but I don't know why the below mentioned code is not functional.

Legend:

• PatternSearchButton is a button
• PatternTextBox is a textbox by which the user enters a value Which the Sheet will filter.

• WsLookup is a function which selects the sheet (completely functional)

  Private Sub PatternSearchButton_Click()
  
      Dim PatternInput As String, PatternCounter As Double, WsSelector As Worksheet
          PatternInput = PatternTextBox.Value
  
      Set WsSelector = WsLookup(GSMListType.Value)
          WsSelector.Range("F:F").AutoFilter Field:=1, Criteria1:=PatternInput
  
          PatternCounter = Application.WorksheetFunction.Subtotal(4, WsSelector.Range("F:F"))
  
          With AvailableNumberList
              .Clear
              For k = 2 To PatternCounter + 1
                  .AddItem WsSelector.Range("A" & k).SpecialCells(xlCellTypeVisible).Value
              Next k
          End With
  
  
  
  End Sub
  

Answer 1

The problem might stem from the fact that you add to the list box the value of a special cell that might not exist if the cell is hidden.

Try for the body of the For loop:

' ... previous code '
If Not WsSelector.Rows(k).EntireRow.Hidden Then
        .AddItem WsSelector.Cells(k, 1).Value
End If
' rest of the code ... '

Also, make sure that AvailableNumberList points to the correct object in your code.

Answer 2

You're are using PatternCounter as the upper limit in your For .. Next but this is being set using the MAX (e.g. 4) subfunction of SUBTOTAL. This might work on sequential numbers in an unfiltered list but it is unlikely to be accurate in a filtered list. Using the COUNT (2/102) or COUNTA (3/103) subfunction might be more appropriate.

You were using SUBTOTAL(4, ...) so I would assume that you are dealing with numbers. Use a straight count on numbers on visible cells in column F and modify the remainder of the code to resemble this.

    PatternCounter = Application.WorksheetFunction.Subtotal(2, WsSelector.Range("F:F"))
    With WsSelector.cells(1,1).currentregion.offset(1,0).SpecialCells(xlCellTypeVisible)
        AvailableNumberList.Clear
        For k = 1 To PatternCounter
            AvailableNumberList.AddItem .cells(k, 1).Value
        Next k
    End With