基于同行值比较的If And语句：新增Range3非零判断需求咨询

Hey Mike, let's get this sorted out for you. Since you already have code that compares Range 1 and Range 2 to pull unique values, adding the check for Range 3 cells not equal to 0 just requires weaving that condition into your existing logic. Let's break this down based on common code patterns you might be using:

Scenario 1: You're looping through rows with a Collection for unique values #

If your original code iterates through each row and uses a Collection to track unique pairs from Range 1 and Range 2, you'll want to add the Range 3 check before adding entries to the Collection. Here's a modified example:

Sub FilterUniqueWithNonZeroCheck()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("YourSheetName") ' Replace with your actual sheet name
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Adjust column to match your data's last row
    
    Dim uniquePairs As New Collection
    Dim i As Long
    Dim range1Val As Variant, range2Val As Variant, range3Val As Variant
    
    On Error Resume Next ' Suppress duplicate key errors in the Collection
    For i = 2 To lastRow ' Skip header row if you have one
        range1Val = ws.Cells(i, "A").Value ' Column for Range 1
        range2Val = ws.Cells(i, "B").Value ' Column for Range 2
        range3Val = ws.Cells(i, "C").Value ' Column for Range 3
        
        ' Combine your original unique check with the new non-zero condition
        ' Only add to the collection if Range 3 isn't 0 AND the pair is unique
        If range3Val <> 0 Then
            uniquePairs.Add Item:=i, Key:=CStr(range1Val) & "|" & CStr(range2Val)
        End If
    Next i
    On Error GoTo 0
    
    ' Example: Copy matching rows to a new location (adjust as needed)
    Dim destRow As Long: destRow = 2
    Dim key As Variant
    For Each key In uniquePairs
        ws.Rows(key).Copy ws.Cells(destRow, "E")
        destRow = destRow + 1
    Next key
End Sub

The key change here is the If range3Val <> 0 Then wrapper around the code that adds items to the unique collection. This ensures only rows where Range 3 isn't zero are considered for unique value filtering.

Scenario 2: You're using Excel's AutoFilter #

If your original code uses AutoFilter to compare Range 1 and Range 2, you can simply add an additional filter criteria for Range 3:

Sub AutoFilterWithNonZeroCheck()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("YourSheetName")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim dataRange As Range
    Set dataRange = ws.Range("A1:C" & lastRow) ' Adjust to include all 3 ranges
    
    ' Clear existing filters first
    ws.AutoFilterMode = False
    
    ' Apply combined filters:
    ' 1. Your original condition for Range 1 vs Range 2 (example: Range1 <> Range2)
    ' 2. Range 3 is not equal to 0
    dataRange.AutoFilter Field:=1, Criteria1:="<>" & ws.Range("B1").Value ' Adjust Field to match Range1's column
    dataRange.AutoFilter Field:=3, Criteria1:="<>" & 0 ' Field matches Range3's column
End Sub

Scenario 3: You're using Excel formulas (instead of VBA) #

If you were using a formula like =UNIQUE(FILTER(A:B, A:A<>B:B)) to get unique values from Range1/Range2, just add the Range3 non-zero condition to the filter:

=UNIQUE(FILTER(A:B, (A:A<>B:B)*(C:C<>0)))

The *(C:C<>0) part acts as an "AND" condition, ensuring only rows where Range3 isn't zero are included in the unique results.

Quick Note #

Make sure to adjust column letters/field numbers in the examples to match your actual Range 1, 2, and 3 locations. The core idea is always the same: only process rows where Range 3's cell value is not zero, alongside your existing unique value logic.

内容的提问来源于stack exchange，提问作者Mike Mann