Banner

Friday, October 26, 2018

Worksheets Autofilter By combobox Userform Excel VBA


                                                     VBA Code
Private Sub ComboBox1_Change()
Dim database(1 To 100, 1 To 4)
Dim My_range As Integer
Dim colum As Byte
On Error Resume Next
Sheet1.range("A2").AutoFilter field:=1, Criteria1:=Me.ComboBox1.Value

For i = 2 To Sheet1.range("A100000").End(xlUp).Row
If Sheet1.Cells(i, 1) = Me.ComboBox1 Then

My_range = My_range + 1
For colum = 1 To 4
database(My_range, colum) = Sheet1.Cells(i, colum)
Next colum
End If
Next i
Me.ListBox1.List = database
End Sub

Private Sub CommandButton1_Click()
'Reset Data Filter
ActiveSheet.ShowAllData
End Sub