title: How to self reference a cell in VBA
body: <p>I have 600k rows and want to remove starting and trailing whitespace. I have the following, but it is rather slow:</p>
  
  <pre><code>Sub Macro1()
  '
  ' Macro1 Macro
  '
  
  '
      Range("D1").Select
      ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
      Range("D1").Select
      Selection.AutoFill Destination:=Range("D1:D4")
      Range("D1:D4").Select
      Columns("D:D").Select
      Selection.Copy
      Range("C1").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
      Columns("D:D").Select
      Application.CutCopyMode = False
      Selection.ClearContents
      Range("C1").Select
  End Sub
  </code></pre>
  
  <p>Is there a way that I can apply the function on itself. I would like to avoid running a function in an empty column, then copying the values to the original column.</p>
  
  <p>I tried <a href="https://stackoverflow.com/questions/25788750/vba-to-fill-formula-down-till-last-row-in-column">VBA to fill formula down till last row in column</a> as well as to speed up the formula. I have a few columns to do this with, and wonder if it is possible to only work on column C and trim the whitespace without the extra computations.</p>
  
  <p>Thanks</p>