Можно ли исключить скрытые строки при поиске дубликатов в Excel?

Я работаю над процедурой в Excel, используя VBA, которая выделяет повторяющиеся строки. Процедура оценивает результат sumproduct функции листа, чтобы определить, имеет ли строка дубликаты.

Вычисленная формула выглядит следующим образом:

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

Пока эта процедура отлично работает, но мне нужно ее игнорировать скрытые строки и столбцы из оценки. Я могу пропустить скрытые строки в столбцах в моих циклах, используя Range.Hidden = False , но я не понял способ исключить скрытые строки и столбцы из SUMPRODUCT.

Я также пробовал повторять каждую строку дважды с помощью двух вложенных циклов и просто сравнивать значения по две строки за раз, но это привело к N-квадрату или O (n 2 ), итерациям, поэтому я отказался от этого метода.

Есть ли способ заставить 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 

    Свойство 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)) 

    Это полный обновленный код. Сначала основная процедура, затем определяемая пользователем функция.

    Если кто-нибудь может объяснить, почему размещение вложенного цикла в UDF происходит быстрее, чем его в основной процедуре, я бы очень оценил это! Еще раз спасибо Oorang!

    Я сделал свою версию 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 
    Interesting Posts
    Давайте будем гением компьютера.