My code is supposed to copy a filtered table (excluding headers) onto a temp sheet, remove duplicates, then use that data to populate the new comboboxes. It is copying the filtered values onto the temp sheet and removing duplicates properly. The problem is that I'm seeing some of the headers from the original table in some of the comboboxes (but not all), and I'm also seeing some values that were filtered out in the table in some of the comboboxes (again, but not all).
So just to clarify, everything works properly until that last block of code. Thank you!!
Dim strFind1 As String 'what to find
Dim FirstAddress1 As String
Dim f1 As Integer
'Find entered value and return all fields for that row
strFind1 = Me.ComboBox17.Value 'what to look for
With Ws.Range("cDept") 'where to look
Sheet2.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:=ComboBox17.Value
Set c1 = .Find(strFind1, LookIn:=xlValues)
'c.Select
If Not c1 Is Nothing Then
With Me
.ComboBox15.Value = c1.Offset(0, -2).Value
.ComboBox16.Value = c1.Offset(0, -1).Value
.ComboBox17.Value = c1.Offset(0, 0).Value
.ComboBox1.Value = c1.Offset(0, 1).Value
.ComboBox19.Value = c1.Offset(0, 2).Value
.cmbAmend.Enabled = False 'allow amendment
r1 = c1.Row
f1 = 0
End With
FirstAddress1 = c1.Address
Else: MsgBox strFind1 & " " & "is not listed." 'search failed
End If
End With
'Copy filtered values to a hidden sheet to populate filtered ComboBoxes
Set rOldList = Sheet2.Range("A2", Sheet2.Range("T65536").End(xlUp))
Sheet3.UsedRange.Clear
rOldList.Copy Destination:=Sheet3.Range("A1")
'Remove duplicates
Sheet3.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo
Sheet3.Range("$B:$B").RemoveDuplicates Columns:=1, Header:=xlNo
Sheet3.Range("$C:$C").RemoveDuplicates Columns:=1, Header:=xlNo
Sheet3.Range("$D:$D").RemoveDuplicates Columns:=1, Header:=xlNo
Sheet3.Range("$E:$E").RemoveDuplicates Columns:=1, Header:=xlNo
'Update comboboxes with filtered values from temp sheet
UserForm1.ComboBox15.RowSource = Sheet3.Range("A1", Sheet3.Range("A65536").End(xlUp)).Address
UserForm1.ComboBox16.RowSource = Sheet3.Range("B1", Sheet3.Range("B65536").End(xlUp)).Address
UserForm1.ComboBox17.RowSource = Sheet3.Range("C1", Sheet3.Range("C65536").End(xlUp)).Address
UserForm1.ComboBox1.RowSource = Sheet3.Range("D1", Sheet3.Range("D65536").End(xlUp)).Address
UserForm1.ComboBox19.RowSource = Sheet3.Range("E1", Sheet3.Range("E65536").End(xlUp)).Address
Bookmarks