title: Preserve null strings when assigning to .Value with VBA
body: <p>(edit: added 2 more restrictions to solutions)</p>
  
  <p>I have a table (listobject wise) in which I need to duplicate certain rows.  I'm using SourceListRow.Range.Value2 = DestListRow.Range.Value2 for efficiency reasons (copying whole ranges in one go).  All formula columns after the copied cell ranges auto-expand perfectly into the new rows and act upon the copied data.  I'm using Excel 2010 here on Windows.</p>
  
  <p>However, although I've already been using this sort of code for eons, I've only now stumbled upon an oddity when using Range.Value / Range.Value2: if you assign an empty string to it, the final cell value will not be an empty string, but it will be Empty.  That is: the data isn't copied over faithfully, and the copy can differ from the source, especially if consecutive formulas use ISBLANK etc. on it.  Therefore the same formula will have different results when working on the copy vs. the source.</p>
  
  <p>See below test code.  Open a new, blank, Excel workbook, go to VBA, add a new module and add the following code:</p>
  
  <pre><code>Sub Test()
    ActiveSheet.Range("a1").Formula = "="""""
    ActiveSheet.Range("b1").Formula = "=isblank(a1)"
    ActiveSheet.Range("c1").Value2 = TypeName(ActiveSheet.Range("a1").Value2)
  
    ActiveSheet.Range("a2").Value2 = ActiveSheet.Range("a1").Value2
    ActiveSheet.Range("b2").Formula = "=isblank(a2)"
    ActiveSheet.Range("c2").Value2 = TypeName(ActiveSheet.Range("a2").Value2)
  
    ActiveSheet.Range("a3").Value2 = ""
    ActiveSheet.Range("b3").Formula = "=isblank(a3)"
    ActiveSheet.Range("c3").Value2 = TypeName(ActiveSheet.Range("a3").Value2)
  
    ActiveSheet.Range("a4").Formula = ActiveSheet.Range("a1").Formula
    ActiveSheet.Range("b4").Formula = "=isblank(a4)"
    ActiveSheet.Range("c4").Value2 = TypeName(ActiveSheet.Range("a4").Value2)
  
    Call ActiveSheet.Range("a1").Copy
    Call ActiveSheet.Range("a5").PasteSpecial(xlPasteValues)
    ActiveSheet.Range("b5").Formula = "=isblank(a5)"
    ActiveSheet.Range("c5").Value2 = TypeName(ActiveSheet.Range("a5").Value2)
  End Sub
  </code></pre>
  
  <p>Then run it and look at the sheet;</p>
  
  <ul>
  <li>B1 tells FALSE (as it should - the cell is not empty), and C1 tells "String" (the cell value is indeed an empty string);</li>
  <li>B2 tells TRUE, even though we just copied over A1's value as-is; C2 tells "Empty", while it should tell "String" if the value was copied over faithfully;</li>
  <li>as a test, B3 tells TRUE, even though we just set it explicitly to an empty string; C3 again tells "Empty" to confirm the data mangling;</li>
  <li>assigning Range.Formula to OtherRange.Formula works (the String data type is preserved in B4), but I do not want to copy formula's, I want to copy only the values!</li>
  <li>as a test I mimic what the GUI would yield in A5, and sure enough, Copy/Paste-As-Values does preserve the is-an-empty-string state...</li>
  </ul>
  
  <p>What to do?</p>
  
  <ul>
  <li>using Range.Copy / Range.PasteSpecial(xlPasteValues) is unacceptable, performance-wise and clipboard-wise;</li>
  <li>using .Formula is also not an option, because I want to copy the values;</li>
  <li>I could iterate over all values in the array, testing if they're a null string and then setting those cell's formula to ="", but I want to copy whole multi-cell ranges in one go to gain efficiency here...</li>
  <li>I can't use autofilters and such because my data lives in a table (a.k.a. ListObject); I could use the table's own autofilter, but people may have it in use already so I'd have to restore it afterwards which is too much of a kludge.</li>
  <li>using Range.Find and Range.Replace is not an option either, since that changes the user's Find/Replace dialog settings.</li>
  </ul>
  
  <p>All ideas appreciated!</p>