Autofilter using variable Criteria VBA

I am trying to put an autofilter on a worksheet using a variable, but it is not working.

 Dim Criteria As Object i = 1 Set Criteria = ActiveSheet.Cells(i, 1) MsgBox (Criteria.Value) ' this returns BC01.03

However, when I try to filter "Criteria.Value" is not returning anything.

Selection.AutoFilter
ActiveSheet.Range("$A$1:$BM$204").AutoFilter Field:=2, Criteria1:=" & Criteria.Value & "

Please advice.

1 Answer

Get rid of the quote marks around your Criteria1 argument:

ActiveSheet.Range("$A$1:$BM$204").AutoFilter Field:=2, Criteria1:=Criteria.Value

Also, just a question why you would use late binding on a Range object? I do use late binding for objects that would require another library/reference, but since Range is part of the Excel object library, I would use early binding. I would Dim Criteria as Range but that's just my preference.