title: VBA Insert string after inserting row
body: <p>I'm trying to accomplish two things; 1) insert rows (and a number in A1) based on a sequential pattern 2) insert the string value of "NA" in the remaining columns of the inserted row. I'm using the script below, part 1 works but part 2 is putting "NA" in all of the columns rather than whats used within the worksheet. Here's a sample of the data:</p>
  
  <pre><code>2001    A   A   A
  2002    A   A   A
  2004    A   A   A
  2005    A   A   A
  </code></pre>
  
  <p>the code should insert 2003 AFTER 2002 with "NA" in columns B:D</p>
  
  <p>Here's the script that I'm currently using: </p>
  
  <pre><code>Sub test()
  Dim i As Long, x, r, cell, CRange As Range
  Dim InputValue As String
  InputValue = "NA"
  'test for sequential number
  For i = Range("a" &amp; Rows.Count).End(xlUp).Row To 2 Step -1
      x = Cells(i, 1) - Cells(i - 1, 1)
      If x &gt; 1 Then
          Rows(i).Resize(x - 1).Insert
      End If
  Next
  'insert row if not sequential
  For Each r In Range("a1", Range("a" &amp; Rows.Count) _
      .End(xlUp)).SpecialCells(4).Areas
      With r.Cells(1).Offset(-1)
          .AutoFill .Resize(r.Rows.Count + 1), 2
      End With
          'Test for empty cell. If empty, fill cell with value given
          For Each cell In Selection
              If IsEmpty(cell) Then
              cell.Value = InputValue
              End If
          Next
  Next
  End Sub
  </code></pre>