title: Is it possible to exclude hidden rows when searching for duplicates in Excel?
body: <p>I am working on a procedure in Excel using VBA that highlights duplicate rows. The procedure evaluates the result of the worksheet function sumproduct to determine if the row has duplicates.</p>
  
  <p>The evaluated formula ends up looking like this:</p>
  
  <pre><code>SUMPRODUCT(--(A1:A10 = A1), --(B1:B10 = B1), --(C1:C10 = C1))
  </code></pre>
  
  <p>So far the procedure works great, but I need it to disregard hidden rows and columns from the evaluation. I can skip over hidden rows in columns in my loops using <code>Range.Hidden = False</code>, but I haven't figured out a way to exclude hidden rows and columns from SUMPRODUCT.</p>
  
  <p>I also tried iterating through every row twice using two nested loops and just comparing values two rows at a time, but that resulted in N-squared, or O(n<sup>2</sup>), iterations, so I gave up on that method.</p>
  
  <p>Is there a way to coerce SUMPRODUCT into ignoring hidden rows, as is possible with the spreadsheet formula SUBTOTAL?</p>
  
  <p>Here is what I have so far using <code>Evaluate(SUMPRODUCT)</code>: Thanks!</p>
  
  <pre><code>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 &lt;&gt; "SUMPRODUCT(" Then
                              strFormula = strFormula &amp; ", "
                          End If
                          strFormula = strFormula _
                          &amp; "--(" &amp; .Columns(lngColumn).Address _
                          &amp; " = " &amp; .Cells(lngRow, lngColumn).Address &amp; ")"
                      End If
                  Next
                  strFormula = strFormula &amp; ")"
  
                  If Evaluate(strFormula) &gt; 1 Then
                      .Rows(lngRow).Font.Color = RGB(255, 0, 0)
                  End If
  
              End If
          Next lngRow
  
      End With
  
  End Sub
  </code></pre>