title: Trying to empty cells to plot gaps in chart
body: <p>I have a series of data, running from cells C169:N174, sourcing a line chart. Cell data is either a number, or empty (Excel empty) - the formula returns either the number or blank text ("").</p>
  
  <p>When the action is triggered, via data validation, I need the formulas in the right-most cells (N169:N174) copied left through C169:C174). To accomplish this, I have this code:</p>
  
  <pre><code>Range("N169:N174").AutoFill Destination:=Range("C169:N174")
  Range("C169:N174").Select
  </code></pre>
  
  <p>After the formulas are copied, I need any cell containing text (""), to be cleared. To accomplish this, I have this code:</p>
  
  <pre><code>Range("C169:M174").SpecialCells(xlCellTypeFormulas, 2).Select
  Selection.ClearContents
  </code></pre>
  
  <p>This code runs for two sets of data. Same code, different cells. Ranges are C169:N174 and C145:N150. I have the code written for range1 (C145:N15) first - formulas copy then cells w/ ("") deleted. Then same action for range2 (C169:N174). Full code:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Application.ScreenUpdating = False
  
  If Target.Address = "$B$2" Then
  
  'mcroCopyTrendedRentFormulas
      Range("N145:N150").AutoFill Destination:=Range("C145:N150"), Type:=xlFillDefault
      Range("C145:N150").Select
  
      'mcroClearTrendedRentEmptyCells
      Range("C145:N150").SpecialCells(xlCellTypeFormulas, 2).Select
      Selection.ClearContents
  
      'mcroCopyAskingRentFormulas
      Range("N169:N174").AutoFill Destination:=Range("C169:M174")
      Range("C169:N174").Select
  
      'mcroClearAskingRentEmptyCells
      Range("C169:M174").SpecialCells(xlCellTypeFormulas, 2).Select
      Selection.ClearContents
  
      Range("A1").Select
  
  End If
  
  End Sub
  </code></pre>
  
  <p>Once the code is triggered, if I click anywhere on the sheet before it has finished running, which I just timed and it took 45 seconds, I get an error saying either:  </p>
  
  <blockquote>
    <p>"1004 Error: No cells were found"  </p>
  </blockquote>
  
  <p>or  </p>
  
  <blockquote>
    <p>"1004 Error: AutoFill method of Range class failed".</p>
  </blockquote>
  
  <p>If I let the code run the entire 45 seconds, it works. If I click anywhere in the sheet and get either error, stop the debugger and try to run it again, I get one of the two errors.  </p>
  
  <p>So maybe speeding up execution is the issue?  </p>
  
  <p>I don't know - open to anything here.</p>