title: SpecialCells in Excel to return value of a formula
body: <p>I wanted a quick simple way to copy cell values to another sheet using <code>SpecialCells</code> in Excel as opposed to looping</p>
  
  <p>My VBA code is as below:</p>
  
  <pre><code>Sub copyMissingData()
      Worksheets("Source").Range("Z4:Z2000").SpecialCells(xlCellTypeConstants).Copy Worksheets("Destination").Range("missing_qbc")
  End Sub
  </code></pre>
  
  <p>My source data <code>Z4:Z20000</code> has formulas that returns a value (texts/numbers/fraction etc) or blank "". I want the copy to ignore the blanks, but copy any other value returned</p>
  
  <p>The VBA code above using <code>SpecialCells(xlCellTypeConstants)</code> doesn't work because of the formula in the source range.</p>
  
  <p>My question: Is there a straightforward way I can use <code>range.specialcells</code> to copy my data from a worksheet to another bearing in mind that source cells contain formulas and the formulas may produce empty string cells which will need to be skipped</p>