Compare 2 column values ​​(in one row)

Question

I need a macro that filters rows whose values ​​for columns A and B are equal or the same, with the difference being 0.

Usually I use autofilter for one range, for example:

ActiveSheet.Range("A2:AX2").AutoFilter Field:=Range("X" & 1).Column, Criteria1:=">0"

In this case, I want to match or compare 2 columns and apply a filter when AB=0

Of course, I could add another column to be the difference between the two, but if I may prefer to avoid it.

PS: Later, I will need another filter for ABC=0 , if your solution is suitable for this.

Answer 1

As far as I know, it is impossible to filter using AutoFilter using criteria where one field should correspond to another field without using an auxiliary field (column). Of course, this is possible using a filter of several fields, but then criteria must be set for each field, and the criteria cannot be a formula that refers to the same row in the table, for example.

The only thing I could think of was using conditional formats and then filtering by color.

Example:

 Sub Makro1() sConditionalFormula = "=AND($A1<>"""",$B1<>"""",$A1=$B1)" 'FormatConditions needs localized formulas, so we create such: Cells(Rows.Count, Columns.Count).Formula = sConditionalFormula sConditionalFormulaLocal = Cells(Rows.Count, Columns.Count).FormulaLocal Cells(Rows.Count, Columns.Count).Clear With ActiveSheet With .Range("A1").CurrentRegion .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:=sConditionalFormulaLocal With .FormatConditions(.FormatConditions.Count) With .Interior .Color = RGB(255, 255, 0) End With End With .AutoFilter Field:=1, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor End With End With End Sub 

Result:

Answer 2

Just add a second row specifying the column and criteria in the Range.AutoFilter Method .

 With Worksheets("Sheet1") 'if there is an active AutoFilter, turn it off If .AutoFilterMode Then .AutoFilterMode = False With .Cells(1, 1).CurrentRegion .AutoFilter field:=1, Criteria1:=0 .AutoFilter field:=2, Criteria1:=0 'filtered on rows that have zero in column A and column B .AutoFilter field:=1 .AutoFilter field:=2 'filter is active but no criteria has been applied .AutoFilter field:=1, Criteria1:=0 .AutoFilter field:=2, Criteria1:=0 .AutoFilter field:=3, Criteria1:=0 'filtered on rows that have zero in column A, column B and column C End With 'turn off AutoFilter completely If .AutoFilterMode Then .AutoFilterMode = False End With