title: Delete Empty Rows with Excel Interop
body: <p>I have user supplied excel files that need to be converted to PDF. Using excel interop, I can do this fine with <code>.ExportAsFixedFormat()</code>. My problem comes up when a workbook has millions of rows. This turns into a file that has 50k+ pages. That would be fine if the workbook had content in all of those rows. Every time one of these files shows up though, there are maybe 50 rows that have content and the rest are blank. How can I go about removing the empty rows so I can export it to a decent sized PDF?</p>
  
  <ol>
  <li><p>I've tried starting at the end row and, one-by-one, using <code>CountA</code> to check if the row has content and if it does, delete it. Not only does this take forever, this seems to fail after about 100k rows with the following error:</p>
  
  <p><em>Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.</em></p></li>
  <li><p>I've tried using <code>SpecialCells(XlCellType.xlCellTypeLastCell, XlSpecialCellsValue.xlTextValues)</code> but that includes a row if any cell has formatting (like a bg color). </p></li>
  <li><p>I've tried using <code>Worksheet.UsedRange</code> and then deleting everything after that but <code>UsedRange</code> has the same problem as point two.</p></li>
  </ol>
  
  <p><hr/>
  This is the code I've tried:</p>
  
  <pre><code>for (int i = 0; i &lt; worksheets.Count; i++)
  {
      sheet = worksheets[i + 1];
      rows = sheet.Rows;
      currentRowIndex = rows.Count;
      bool contentFound = false;
  
      while (!contentFound &amp;&amp; currentRowIndex &gt; 0)
      {
          currentRow = rows[currentRowIndex];
  
          if (Application.WorksheetFunction.CountA(currentRow) == 0)
          {
              currentRow.Delete();
          }
          else
          {
              contentFound = true;
          }
  
          Marshal.FinalReleaseComObject(currentRow);
          currentRowIndex--;
      }
  
      Marshal.FinalReleaseComObject(rows);
      Marshal.FinalReleaseComObject(sheet);
  }
  </code></pre>
  
  <hr/>
  
  <pre><code>for (int i = 0; i &lt; worksheets.Count; i++)
  {
      sheet = worksheets[i + 1];
      rows = sheet.Rows;
  
      lastCell = rows.SpecialCells(XlCellType.xlCellTypeLastCell, XlSpecialCellsValue.xlTextValues);
      int startRow = lastCell.Row;
  
      Range range = sheet.get_Range(lastCell.get_Address(RowAbsolute: startRow));
      range.Delete();
  
      Marshal.FinalReleaseComObject(range);
      Marshal.FinalReleaseComObject(lastCell);
      Marshal.FinalReleaseComObject(rows);
      Marshal.FinalReleaseComObject(sheet);
  }
  </code></pre>
  
  <p>Do I have a problem with my code, is this an interop problem or maybe it's just a limitation on what Excel can do? Is there a better way to do what I'm attempting?</p>