AutoFilter, why does it always keeps 1st row of range?

83 views Asked by At

I have a dynamic range name for a Client database, I use the following code to filter it according to a textbox I have. Why does it always show up the first client, whatever I type on textbox?

'Dynamic Listing - Client

Private Sub filterClientListing()
Dim rngFilter As range

'Temp String
Dim temp As String
temp = Me.txtClient.Value & "*"


'Variables Definition
Dim wsFData As Worksheet
Set wsFData = Worksheets("FilteredLists")

Dim wsData As Worksheet
Set wsData = Worksheets("Lists")

'Filter Sheet Clear
wsFData.range("A2:C1000").Clear

wsData.AutoFilterMode = False
Set rngFilter = wsData.range("ClientList")

With rngFilter
    .AutoFilter Field:=1, Criteria1:=temp
    .Copy Destination:=wsFData.range("A1").Offset(1, 0)
    .AutoFilter
End With
End Sub
1

There are 1 answers

0
Daniel Sousa On

My solution was to instead use a combobox. Only thing I need help now is to show more than one line inside the combobox list. I can search my range fine and display the results on combo but its displaying all in one line wich I can press the arrows to go up or down.

How to solve this 1 line display?

Private Sub cboClient_Change()
Dim v As Variant
Dim i As Long
cboClient.DropDown
With Me.cboClient
    If .Value <> "" And .ListIndex = -1 Then
        v = Worksheets("Lists").range("ClientList").Value
        .Clear
        For i = LBound(v, 1) To UBound(v, 1)
            If LCase(v(i, 1)) Like LCase(.Value) & "*" Then
                .AddItem v(i, 1)
            End If
        Next i
    Else
    .List = Worksheets("Lists").range("ClientList").Value
    End If
End With
End Sub

Populated my combobox this way:

Private Sub UserForm_Initialize()

With Me.cboClient
    .RowSource = ""
    .ColumnCount = 1
    .AutoWordSelect = False
    .MatchEntry = fmMatchEntryNone
    .List = Worksheets("Lists").range("ClientList").Value
End With

End Sub