title: SpecialCells(xlCellTypeVisible) also includes hidden/filtered cells
body: <p>I have a dataset that I'm using a filter on.  I simply want to calculate the total values in column N, that are visible.  The data starts in row 2, and ends at row 2047.</p>
  
  <p>I saw <a href="https://stackoverflow.com/a/32556476/4650297">this thread</a> but it gives me the same type of issue I'm having.  </p>
  
  <p>Here's my function:</p>
  
  <pre><code>Function sumVisible() As String
  Dim rng As Range
  Set rng = Range("N2:N2047").SpecialCells(xlCellTypeVisible)
  ' Debug.Print "Range: " &amp; rng.Address &amp; ", Sum: " &amp; WorksheetFunction.Sum(rng)
  sumVisible = Format(WorksheetFunction.Sum(rng), "$#,###.##")
  End Function
  </code></pre>
  
  <p>With my current filter, my header row (1) is visible, as are rows 901 to 937.  So, I want to sum N901:N937.</p>
  
  <p>However, the <code>rng</code> keeps getting set to <code>$N$2:$N$2047</code>.  I expected it to be <code>$N$901:$N$937</code>. </p>
  
  <p>Using the function that is given to the thread I linked to above, I get a range of <code>$N$2:$N$937</code>...so at the very least, I'm getting the <em>end</em> row correctly, but not the start row.</p>
  
  <p><em>But!</em> if I type <code>Range("N2:N2047").SpecialCells(xlCellTypeVisible).Select</code> in the Immediate Window, outside of a macro, it correctly selects just the visible cells.  And one step further, doing <code>?Range("N2:N2047").SpecialCells(xlCellTypeVisible).address</code> correctly returns <code>$N$901:$N$937</code>.</p>
  
  <p>What may be going wrong?</p>
  
  <p>Edit: I just found that doing <code>=SUBTOTAL(9,N1:N2047)</code> will just sum the visible cells, so I'm using that.  But my question still stands - why isn't <code>SpecialCells(xlCellTypeVisible)</code> working correctly in the macro?</p>