title: In VBA (Excel) how can Column A insert an ID header followed by 1-X UNTIL it reaches the empty cell representing the highest row with data?
body: <p>I am trying to create a macro that will insert a column, which is to be the first column in the spreadsheet (A) while shifting all original columns over 1 column to the right.</p>
  
  <p>I then need this first column to create the header "ID" with each one numerically counting the rows:</p>
  
  <p><em>[A]</em></p>
  
  <p><strong>ID</strong></p>
  
  <p>1</p>
  
  <p>2</p>
  
  <p>3</p>
  
  <p>I only want the numbering to stop once it has reached the last relevant row in the spreadsheet. I was able to generate the following VBA by doing what I would normally do to accomplish this task while recording the macro and ended up with this:</p>
  
  <pre><code>Sub InsertID()
  '
  ' InsertID Macro
  ' Add first column to be 1-##
  '
  ' Keyboard Shortcut: Ctrl+Shift+N
  '
      Columns("A:A").Select
      Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      Range("A1").Select
      ActiveCell.FormulaR1C1 = "ID"
      Range("A2").Select
      ActiveCell.FormulaR1C1 = "1"
      Range("A3").Select
      ActiveCell.FormulaR1C1 = "2"
      Range("A2:A3").Select
      Selection.AutoFill Destination:=Range("A2:A522")
      Range("A2:A522").Select
      Range("A1").Select
  End Sub
  </code></pre>
  
  <p>Obvoiusly, this doesn't work for my situation. The template I was using only had 521 rows. This number is going to be a variable which can usually be determined by the number of rows in the original column A (Which is now column be after running this macro).</p>
  
  <p>I have extensively looked into how to create a variable for number of rows in a specific column but have been unable to find a question that has similar enough parameters even though it seems so simple.</p>
  
  <p>Thanks in advance</p>