title: How can I assign the SpecialCells of one range to a new range?
body: <p>I know that I can accomplish this via iterating over the first range, but I'm curious to see if I can accomplish it using the <code>SpecialCells</code> property.</p>
  
  <p>Say I have a column of names with empty cells in between:</p>
  
  <pre><code> A     B    C
  Jon
  Jim
  Sally
  
  Jane
  
  
  Mary
  </code></pre>
  
  <p>If I want to use VBA to copy over just the used cells, I can say</p>
  
  <pre><code>Range("A1:A8").SpecialCells(xlCellTypeConstants, xlTextValues).Copy
  Range("C1:C"&amp;Range("A1:A8").SpecialCells(xlCellTypeConstants, xlTextValues).Count).PasteSpecial
  </code></pre>
  
  <p>and end up with</p>
  
  <pre><code> A     B    C
  Jon        Jon
  Jim        Jim
  Sally      Sally
             Jane    
  Jane       Mary
  
  
  Mary
  </code></pre>
  
  <p>Instead, I'd like to be able to do this without having to paste a range anywhere.</p>
  
  <p>What I want to be able to do is have a range containing <code>[Jon,Jim,Sally,Jane,Mary]</code>, but if I try <code>Set rng = Range("A:A").SpecialCells(xlCellTypeConstants,xlTextValues)</code>, I either end up with the spaces as elements of the range, or using a hard-coded range of cells, with one that counts only <code>[Jon, Jim, Sally]</code> before it hits the space.</p>
  
  <p>I'd like to be able to use the range elsewhere in the code, and I think the SpecialCells is a nice compact way of doing it, but is my only alternative to do it in a loop and compare cells as <code>&lt;&gt; ""</code>?</p>