PDA

View Full Version : Excel 2013>VBA>AutoFilter>Multiple Fields Multiple Criteria



aworthey
07-25-2016, 11:38 AM
Hello,

I'm encountering a problem when I try to manipulate multiple fields with one unique criteria for each field. What happens is that when the second filter is attempted my entire used range is hidden. I can manually make these changes on my data set and achieve the desired results. But the code hides all used rows.

Here's the code:


Private Sub CommandButton1_Click()

Dim srchString As Variant
Dim srchString2 As Variant
Dim mtchString As Variant
Dim rng As Range
Dim uniques()
Dim myUniques As Variant
Dim i As Long
Dim are As Variant
Dim cll As Variant

Range("GeneratorModels").AutoFilter

srchString = Me.ComboBox1.Value
srchString2 = Me.ComboBox2.Value
MsgBox srchString2

ThisWorkbook.Sheets("ES DB").Range("A1").AutoFilter Field:=8, Criteria1:=srchString2

With Range("GeneratorModels")
Set mtchString = .Find(srchString, LookIn:=xlValues)
MsgBox mtchString.Column - 28
.AutoFilter Field:=mtchString.Column - 28, Criteria1:="1"
End With



Set rng = ThisWorkbook.Sheets("ES DB").Range("ESDescription")
Set myUniques = Intersect(rng, rng.Offset(1, 0)).SpecialCells(xlCellTypeVisible)
ReDim uniques(1 To myUniques.Cells.count / myUniques.Columns.count)
i = 1
For Each are In myUniques.Areas
For Each cll In are.Cells
uniques(i) = cll.Value
i = i + 1
Next cll
Next are

Me.ListBox1.List = uniques

End Sub

mancubus
07-26-2016, 01:44 AM
post your workbook.

if autofilter method returns no rows (other than header), it means there are no rows which meet the criteria or criteria is/are wrong.

aworthey
07-26-2016, 06:17 AM
post your workbook.

if autofilter method returns no rows (other than header), it means there are no rows which meet the criteria or criteria is/are wrong.

I was able to perform the filtering manually, and it returned plenty of results. Here's what I finally got to work:


Private Sub CommandButton1_Click()

Dim srchString As Variant
Dim srchString2 As Variant
Dim mtchString As Variant
Dim rng As Range
Dim uniques()
Dim myUniques As Variant
Dim i As Long
Dim are As Variant
Dim cll As Variant

srchString = Me.ComboBox1.Value
srchString2 = Me.ComboBox2.Value

With Sheets("ES DB")
.AutoFilterMode = False
With .Range("A1")
.AutoFilter Field:=8, Criteria1:=srchString2
End With
With Range("GeneratorModels")
Set mtchString = .Find(srchString, LookIn:=xlValues)
.AutoFilter Field:=mtchString.Column, Criteria1:="1"
End With
End With

Set rng = ThisWorkbook.Sheets("ES DB").Range("ESDescription")
Set myUniques = Intersect(rng, rng.Offset(1, 0)).SpecialCells(xlCellTypeVisible)
ReDim uniques(1 To myUniques.Cells.count / myUniques.Columns.count)
i = 1
For Each are In myUniques.Areas
For Each cll In are.Cells
uniques(i) = cll.Value
i = i + 1
Next cll
Next are

Me.ListBox1.List = uniques

End Sub