title: excel vba copy down over blank cells
body: <p>column A has data like this (ie frequent blank cells):</p>
  
  <pre><code>HEADING  &lt;-- this is A1
  kfdsl
  fdjgnm
  fdkj
  
  
  gdfkj
  4353
  
  fdjk  
  
  
  
  blah     &lt;-- this is A14 
  </code></pre>
  
  <p>I'm trying to copy it to a new range in the D column but make it appears like this:</p>
  
  <pre><code>HEADING  &lt;-- this is D1
  kfdsl
  fdjgnm
  fdkj
  fdkj
  fdkj
  gdfkj
  4353
  4353
  fdjk  
  fdjk
  fdjk
  fdjk
  blah     &lt;-- this is D14 
  </code></pre>
  
  <p>here is my code so far:</p>
  
  <pre><code>For i = 0 To UBound(origincells) 
   numrows = originsheet.Range(Left(origincells(i), 1) &amp; "65536").End(xlUp).Row - 1
   originsheet.Range(origincells(i) &amp; ":" &amp; Left(origincells(i), 1) &amp; numrows).Copy
   destsheet.Range(destcells(i) &amp; ":" &amp; Left(destcells(i), 1) &amp; (Val(Right(origincells(i), 1)) + numrows)).PasteSpecial
  Next
  </code></pre>