VBA Autofilter to Clear cells in Field2 that match Field1 criteria

[Giga_Me]

I just started using autofilter to avoid looping as much in my code but am hoping someone can help me to understand how I can use the autofilter in VBA to, for example, clear a cell in Field2 where the criteria in Field2 and Field1 match.

Would I use offset somehow like (this example will obviously not work )

Cells(1, 1).AutoFilter field:=2, Criteria1:="=" & Cells.Offset(0, -1)
rng.SpecialCells(xlCellTypeVisible).Clear

Thanks,

Giga_Me

 

[jbeaucaire]

That's not how Autofilter works. You would need to add a new column temporarily with a "test" formula in it, thus marking all the rows for clearing.

So in M2 you could put this to test A2 and B2:

=A2=B2

Copy that down, then filter that column for TRUE. Then clear the visible cells in A or B, whichever it is you're trying to do.

 

[Giga_Me]

Thanks jbeaucaire...perfect solution.