Advance filter using an array for the criteriaSet matrices = .Range(matrix, .Cells(.Rows.Count, matrix.Column).End(xlUp))

[Luin29]

Hi everyone. I hopefully have a quick question that can be answered: How do I use Excel's advance filter with an array value for the criteria?

Here is part of my code so far (the last line is the where I am currently stumped at):

With Sheets("temp")
    Set matrices = .Range("B2", .Cells(.Rows.Count, matrix.Column).End(xlUp))
End With 
  
    matrices.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    ReDim M_array(1 To matrices.SpecialCells(xlCellTypeVisible).Row)
    For Each c In matrices.SpecialCells(xlCellTypeVisible)
        If c.Value <> "Matrix" Then
            M_array(i) = c.Value
            i = i + 1
        End If
    Next c
    ActiveSheet.ShowAllData
.
.
.
    For m = LBound(M_array) To LBound(M_array)
    If CountUnique(Range(units_c.Offset(1), Cells(Rows.Count, units_c.Column).End(xlUp))) > 1 Then
        answer = MsgBox("More than one type of " & M_array(m) & " units reported. Do you want to convert the units?", vbYesNo + vbQuestion, "Multiple Units")
        Select Case answer
            Case vbYes
            UserForm16.Show
            matrices.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=M_array(m), Unique:=True
<strike>
</strike>

Thanks for any and all of the help.
​

 

[pgc01]

Hi

The CriteriaRange, like the name says, must be a range, not an array.

You can write the array into a range and then use the Advanced Filter with that range.

 

[Luin29]

Thanks pgc01. I should have thought on that question a bit more before posting. Figured out that I needed to use Autofilter with transpose(array) for the criteria.

 

[pgc01]

I'm glad your problem is solved.