在 Excel 中搜索重复项时是否可以排除隐藏行？

Question

我正在 Excel 中使用 VBA 编写一个突出显示重复行的过程。该过程评估工作表函数 sumproduct 的结果以确定该行是否有重复项。

评估的公式最终看起来像这样：

SUMPRODUCT(--(A1:A10 = A1), --(B1:B10 = B1), --(C1:C10 = C1))

到目前为止，该过程运行良好，但我需要它忽略评估中的隐藏行和列。我可以使用 Range.Hidden = False 跳过循环中列中的隐藏行，但我还没有找到从 SUMPRODUCT 中排除隐藏行和列的方法。

我还尝试使用两个嵌套循环对每一行进行两次迭代，并且一次只比较两行的值，但这导致了 N 平方或 O(n²) 迭代，所以我放弃了就那个方法。

有没有办法强制 SUMPRODUCT 忽略隐藏行，就像电子表格公式 SUBTOTAL 一样？

这是我迄今为止使用 Evaluate(SUMPRODUCT) 得到的结果：谢谢！

Private Sub ShowDuplicateRows()

    Dim lngRow As Long
    Dim lngColumn As Long
    Dim strFormula As String

    With Selection

        For lngRow = 1 To .Rows.Count
            If Not .Rows(lngRow).Hidden Then

                strFormula = "SUMPRODUCT("
                For lngColumn = 1 To .Columns.Count
                    If Not .Columns(lngColumn).Hidden Then
                        If strFormula <> "SUMPRODUCT(" Then
                            strFormula = strFormula & ", "
                        End If
                        strFormula = strFormula _
                        & "--(" & .Columns(lngColumn).Address _
                        & " = " & .Cells(lngRow, lngColumn).Address & ")"
                    End If
                Next
                strFormula = strFormula & ")"

                If Evaluate(strFormula) > 1 Then
                    .Rows(lngRow).Font.Color = RGB(255, 0, 0)
                End If

            End If
        Next lngRow

    End With

End Sub

Answer 1

RowHeight/Hidden 属性不会暴露给任何公式。解决方案必须是用 VBA 编写的。实现此目的的一种方法是创建一个用户定义的公式 (UDF) 来执行您想要的操作，然后在 sumproduct 公式中使用它。

Public Function IsVisible(ByVal rng As Excel.Range) As Variant
    Dim varRtnVal As Variant
    Dim lRow As Long, lCol As Long
    Dim ws As Excel.Worksheet
    ReDim varRtnVal(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    For lRow = 1& To rng.Rows.Count
        For lCol = 1& To rng.Columns.Count
            varRtnVal(lRow, lCol) = CDbl(-(rng.Cells(lRow, lCol).RowHeight > 0&))
        Next
    Next
    IsVisible = varRtnVal
End Function

那么你的公式将如下所示：

=SUMPRODUCT(IsVisible($A$2:$A$11),--($A$2:$A$11=1),--($B$2:$B$11=1),--($C$2:$C$11=1))

Answer 2

这是完整更新的代码。首先是主程序，然后是用户定义的函数。

如果有人能解释为什么将嵌套循环放入 UDF 中比将其放入主程序中更快，我将不胜感激！再次感谢奥朗！

我使 IsVisible UDF 的版本更加灵活。它可以使用作为参数传递的范围，或者如果没有传递，则使用 Application.Caller。

Private Sub ShowDuplicateRows()

    Dim lngRow As Long
    Dim lngColumn As Long
    Dim strFormula As String

    With Selection

        For lngRow = 1 To .Rows.Count
            If Not .Rows(lngRow).Hidden Then

                strFormula = "SUMPRODUCT(--(ISVISIBLE(" _
                & .Columns(1).Address & "))"
                For lngColumn = 1 To .Columns.Count
                    If Not .Columns(lngColumn).Hidden Then
                        strFormula = strFormula _
                        & ", --(" & .Columns(lngColumn).Address _
                        & " = " & .Cells(lngRow, lngColumn).Address & ")"
                    End If
                Next
                strFormula = strFormula & ")"

                If Evaluate(strFormula) > 1 Then
                    .Rows(lngRow).Font.Color = RGB(255, 0, 0)
                Else
                    .Rows(lngRow).Font.ColorIndex = xlAutomatic
                End If

            End If
        Next lngRow

    End With

End Sub

Public Function IsVisible(Optional ByVal Reference As Range) As Variant

    Dim varArray() As Variant
    Dim lngRow As Long
    Dim lngColumn As Long

    If Reference Is Nothing Then Set Reference = Application.Caller

    With Reference

        ReDim varArray(1 To .Rows.Count, 1 To .Columns.Count)

        For lngRow = 1 To .Rows.Count
            For lngColumn = 1 To .Columns.Count
                varArray(lngRow, lngColumn) _
                = Not .Rows(lngRow).Hidden _
                And Not .Columns(lngColumn).Hidden
            Next lngColumn
        Next lngRow

    End With

    IsVisible = varArray

End Function