title: Copying visible/filtered rows efficiently in excel
body: <p>I am working with some very large datasets (various sheets with 65K+ rows and many columns each). I am trying to write some code to copy filtered data from one sheet to a new empty sheet as fast as possible, but have not had much success so far. </p>
  
  <p>I can include the rest of the code by request, but all it does is calculates the source and destination ranges (srcRange and destRange). The time taken to calculate these is negligible. The vast majority of the time is being spent on this line (4 minutes 50 seconds to be precise):</p>
  
  <pre><code>srcRange.Rows.SpecialCells(xlCellTypeVisible).Copy Destination:=destRange
  </code></pre>
  
  <p>Additionally I've tried this:</p>
  
  <pre><code>destRange.Value = srcRange.Rows.SpecialCells(xlCellTypeVisible).Value
  </code></pre>
  
  <p>But it doesn't work properly when there's a filter.</p>
  
  <pre><code>Function FastCopy(srcSheet As String, srcCol As String, destSheet As String, destCol As String)
  
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
  
      Dim srcRange As Range
      Dim destRange As Range
  
      Set srcRange = GetColumnRangeByHeaderName(srcSheet, srcCol, -1)
      Set destRange = GetColumnRangeByHeaderName(destSheet, destCol, srcRange.Rows.Count)
  
  
      'destRange.Value = srcRange.Rows.SpecialCells(xlCellTypeVisible).Value
  
      srcRange.Rows.SpecialCells(xlCellTypeVisible).Copy Destination:=destRange
  
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationManual
  End Function
  </code></pre>
  
  <p>This is a slow, dual core machine with 2GB of RAM running excel 2010. Results will obviously vary on a faster machine.</p>