title: How to delete all rows from excel except header in c#
body: <p>How do I delete all rows in an excel spreadsheet except the header in c#?</p>
  
  <p>I am trying to do this using the <code>Microsoft.Office.Interop.Excel;</code> library</p>
  
  <p>I now have this code</p>
  
  <pre><code>        Range xlRange = ws.UsedRange;
          int rows = xlRange.Rows.Count;
  
          Console.WriteLine(rows);
  
          for (int i = 2; i &lt;= rows; i++)
          {
              ((Range)ws.Rows[i]).Delete(XlDeleteShiftDirection.xlShiftUp);
          }
  </code></pre>
  
  <p>But it's not deleting all the rows, I think because it's deleting the rows when it hits a certain number that row is no longer there, what have I done wrong?</p>
  
  <p>I managed to do it, I have started from the bottom instead of the top, so now my loop is </p>
  
  <pre><code>        for (int i = rows; i != 1; i--)
          {
              ((Range)ws.Rows[i]).Delete(XlDeleteShiftDirection.xlShiftUp);
          }
  </code></pre>
  
  <p><strong>Solution</strong></p>
  
  <pre><code>var range = (Range)ws.Range[ws.Cells[2, 1], ws.Cells[ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count]];
  range.Delete(XlDeleteShiftDirection.xlShiftUp);
  </code></pre>
  
  <p>There had been a change in v4 of .Net that using <code>get_Range</code> stopped working</p>