比较两列值(同一行)

I need a macro that filters rows which values for columns A and B are equal, or which is the same, which difference is 0.

我需要一个宏来过滤a和B列值相等的行，或者相同的行，差值为0。

Normally I apply autofilter for a single range, like this:

通常我对单个范围应用自动过滤器，如下所示:

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

ActiveSheet.Range(“A2:AX2”)。自动过滤器字段:=范围(“X”& 1).列，标准:=“>”

In this case I want to match or compare 2 columns and apply a filter when A-B=0

在这种情况下，我想匹配或比较两列，并在a - b =0时应用一个过滤器

Of course I could add another column to be the difference between these 2, but if I can I prefer to avoid it.

当然，我可以添加另一列来表示这两个的差值，但如果可以的话，我宁愿避免它。

PS: Later, I will need another filter for A-B-C=0, in case your solution fits this one too.

稍后，我需要另一个A-B-C=0的过滤器，以防你的解决方案也适合这个。

2 个解决方案

#1

As far as I know it is not possible filtering with AutoFilter by using a criteria, where one field must match another field, without using a helper field (column). Surely it is possible using a multiple fields filter but then for each field a criteria must be set and a criteria cannot be a formula which refers to the same row in the table for example.

就我所知，不可能通过使用条件(其中一个字段必须与另一个字段匹配，而不使用帮助字段(列))来进行自动筛选。当然，使用多个字段过滤器是可能的，但是对于每个字段，必须设置一个标准，而一个标准不能是引用表中相同行的公式。

The only thing I could think is using conditional formats and then filtering by colors.

我唯一能想到的就是使用条件格式，然后根据颜色进行过滤。

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:

结果:

#2

Just add a second line specifying column and criteria to 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

#1