Hi all, I am editing a workbook that I found on this forum, and I am trying to create a macro button that will allow a person to select either 1 sub-category criteria or several (up to 10 would be perfect, but 23 would be ideal). I am fine with creating numerous buttons to do so if that would be necessary.
I noticed while using the workbook that it will automatically populate only 7 rows in the Results field, so if there are less than 7 fields then there will be duplicates - if there are more then results will be missing. Can anyone help solve this? Below is some code that I have tried but it doesn't appear to be helping:
Private Sub CommandButton1_Click()
Dim i As Long, rData As Range, vItem(), j As Long
Sheets("RESULTS").UsedRange.Offset(1).Clear
With Sheets("FINAL_TABLE")
.AutoFilterMode = False
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
j = j + 1
ReDim Preserve vItem(j)
vItem(j) = Me.ListBox1.List(i)
End If
Next i
For j = LBound(vItem) To UBound(vItem)
.Range("A1").AutoFilter Field:=3, Criteria1:=vItem(j)
With .AutoFilter.Range
On Error Resume Next
Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rData Is Nothing Then
rData.Copy Sheets("RESULTS").Cells(Rows.Count, 1).End(xlUp)(2)
End If
End With
.ShowAllData
Next j
End With
Unload UserForm4
End Sub
Could anyone please help me out? Thank you so much in advance!
Bookmarks