Autofilter with Items selected in ListBox

[chikitin]

Hi Every Body,
I have a ListBox with Multiselected Items (with checkBox), and I have to do Autofilter in My sheet: Table, to find matching rows. perhaps, there is another idea to do this?

I've done this, but it doesn't run: error at the end about: .Columns(Columns.Count).AutoFilter field:=1, Criteria1:="F"
HELP!!!!

Code: Select all

Dim i As Long, LastLig As Long
 
Application.ScreenUpdating = False
With Sheets("Table")
   LastLig = .Cells(Rows.Count, "B").End(xlUp).Row
   .Columns(Columns.Count).Delete
   For i = 0 To ListBox1.ListCount - 1
      If ListBox1.Selected(i) Then
         .Range("B1:B" & LastLig).AutoFilter field:=1, Criteria1:="=" & CDbl(Me.ListBox1.List(i))
         .Range(.Cells(2, Columns.Count), .Cells(LastLig, Columns.Count)).SpecialCells(xlCellTypeVisible).Value = "F"
      End If
   Next i
   .Range("B1").AutoFilter
   .Columns(Columns.Count).AutoFilter field:=1, Criteria1:="F"
End With
 

[HansV]

Welcome to Eileen's Lounge!

In a quick test, your code appears to work OK. Could you attach a small sample workbook(without sensitive data) in which the error occurs?

Note: I'd use .Columns.Count instead of Columns.Count, to make sure that you use the column count of the Table sheet.

[chikitin]

Thanks Hans,
Here is my WorkBook, it's in French, The userForm is the "Employe" (because there are many others...), so in this UserForm: I have a ComboBox1 where I chose the usine(columnC), Where work some peoples who are in column B, I Choose in the listBox1 (with Checkbox) to show in ListBox2 some names.
the code is in CommandButton2_Click, and I'd like that the sheet "Table" is filtered by the names who are choosed in ListBox2.
Thanks for your help

[HansV]

I'm confused - ListBox2 is not multi-select. Moreover, ListBox2 is filled with text strings (names), so the line

.Range("B1:B" & LastLig).AutoFilter field:=1, Criteria1:="=" & CDbl(Me.ListBox2.List(i))

throws an error because of the use of CDbl. Are you Employe is the correct userform?

[chikitin]

Yes, Employe is the correct UserForm, I understand that you say I mustn't use CDbl, I use it for a textBox and I think......
So what do I use instead of CDbl with the ListBox2.List?

[HansV]

You can change the line to

.Range("B1:B" & LastLig).AutoFilter Field:=2, Criteria1:="=" & Me.ListBox2.List(i)

I changed Field:=1 to Field:=2 because Excel looks at the entire range, and you want to filter on the name column. And I simply removed CDbl.

[chikitin]

Thanks a lot Hans.