Advanced filter is used to filter by a range of criteria, but to get a result based on one criteria gotten by a combobox value, a simple autofilter will do the trick as so:
Private Sub ComboBox1_Change()
Dim lastr As Integer
lastr = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("A1:C" & lastr).AutoFilter Field:=3, Criteria1:=ComboBox1.Value, Operator:=xlFilterValues
End Sub
Explanation: the lastrow is dimmed to take an integer (numerical whole value). Then it is determined with the most reliable lastrow method to date.
Then the range A1 to C and your lastrow (So effectively A1:C7 for example) and an autofilter is applied. The field is the column within your range it filters on, 3 in this case means column C (if your range was B:D instead, 3 would mean the 3rd column in the range so column D). The criteria is the value taken directly from your combobox with ComboBox1.value. The operator xlFilterValues means it will filter by the values found in the column, which will work even if the column has formulas.
Please note: You can get the value directly from the combobox, there is no need to put it in a cell first, but you can do so if you want. Also finding the last row of your data isn't strictly necessary in this case either, just put range as Range("A:C") would do as well.
EDIT:
I have also explored the .advancedfilter method, and even though I don't know much about it, here are my findings:
Private Sub ComboBox1_Change()
Dim sh As Worksheet: Set sh = ThisWorkbook.Sheets("Sheet1")
Dim lastr As Integer
If sh.FilterMode Then sh.ShowAllData
lastr = sh.Range("A" & Rows.Count).End(xlUp).Row
sh.Range("A1:C" & lastr).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=("Table1[#All]"), Unique:=False
End Sub
Notes: There is a combobox with linkedcell F2, the range F1:F2 is in a table called table1. I didn't get the advancedfilter to work without putting it in a table due to the advancedfilter properties. The header of table 1 needs to be exactly the header of the column you want to filter by. If you set the header to the same as column 1, it will search for the value in the table in column 1.
Explanation: The worksheet dim sets the worksheet to the current file sheet1. This isn't strictly necessary, but comes in handy later on.
lastr is the same as last time, determine the last row and apply your filter to this range. Not strictly necessary again, but in this case speeds it up quite a bit.
The If sh.FilterMode Then sh.ShowAllData is important for when you select a new value. It basically resets the filter so a new filter can be applied. It detects if there is a filter applied to your sheet, and then sets it to show all data. If no filter is applied this is not needed, but if you leave out the test and just put in the showalldata it will fail since there is nothing else to show and throw an error, therefore the test. If the filter is not reset when applying a new one it will throw an error as well, since the data isn't found (because it's filtered out by the previous one).
Then the advancedfilter is applied to the range with your data as previously. The criteria is to note here: it takes the full range of Table1, in this case just the combobox value below the header identical to column 2. The advancedfilter then hides all rows that don't match these data.
Notes: The advancedfilter can take much more criteria than just the one combobox value.
For every row in Table1, it will handle it as an OR statement: So if in Table 1 I have a header similar to header 2 with below it value A, and a header similar to header 3 with two rows below it value B, it will search the range for column 2 to be A OR column 3 to be B, and hides everything that doesn't match.
Header2 | Header3 'this will return rows with A under Header2 and rows with B under Header3
A | 0
0 | B
All criteria on the same row is used as an AND statement. If I have the two headers as above, but the A and B are on the same row, then the filter will search for rows with both A and B on the same line.
Header2 | Header3 'this will return rows with both A under Header2 and B under Header3
A | B
This can be expanded with as many columns and values as your data has. Just note that your table cannot have empty values, as this will match with ALL your data. This is the reason advancedfilter, if used right, is much more powerful than .autofilter as autofilter will only search one column by default and is finicky to work with multiple criteria.
