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
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