title: Fastest method to remove Empty rows and Columns From Excel Files using Interop
body: <p>I have  a lot of excel files that contains data and it contains empty rows and empty columns.
  like shown bellow</p>
  
  <p><a href="https://i.stack.imgur.com/BirO8.png" rel="noreferrer"><img src="https://i.stack.imgur.com/BirO8.png" alt="Excel preview"></a></p>
  
  <p>I am trying to remove Empty rows and columns from excel using interop.
  I create a simple winform application and used the following code and it works fine.</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim lstFiles As New List(Of String)
  lstFiles.AddRange(IO.Directory.GetFiles(m_strFolderPath, "*.xls", IO.SearchOption.AllDirectories))
  
  Dim m_XlApp = New Excel.Application
  Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
  Dim m_xlWrkb As Excel.Workbook
  
  For Each strFile As String In lstFiles
      m_xlWrkb = m_xlWrkbs.Open(strFile)
      Dim m_XlWrkSheet As Excel.Worksheet = m_xlWrkb.Worksheets(1)
      Dim intRow As Integer = 1
  
      While intRow &lt;= m_XlWrkSheet.UsedRange.Rows.Count
          If m_XlApp.WorksheetFunction.CountA(m_XlWrkSheet.Cells(intRow, 1).EntireRow) = 0 Then
              m_XlWrkSheet.Cells(intRow, 1).EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp)
          Else
              intRow += 1
          End If
      End While
  
      Dim intCol As Integer = 1
      While intCol &lt;= m_XlWrkSheet.UsedRange.Columns.Count
          If m_XlApp.WorksheetFunction.CountA(m_XlWrkSheet.Cells(1, intCol).EntireColumn) = 0 Then
              m_XlWrkSheet.Cells(1, intCol).EntireColumn.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft)
          Else
              intCol += 1
          End If
      End While
  Next
  
  m_xlWrkb.Save()
  m_xlWrkb.Close(SaveChanges:=True)
  
  Marshal.ReleaseComObject(m_xlWrkb)
  Marshal.ReleaseComObject(m_xlWrkbs)
  m_XlApp.Quit()
  Marshal.ReleaseComObject(m_XlApp)
  </code></pre>
  
  <p>But when cleaning big excel files it takes a lot of time.
  Any suggestions for optimizing this code? or another way to clean this excel files faster? Is there a function that can delete empty rows in one click?</p>
  
  <p>I don't have problem if answers are using C#</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I uploaded a sample file <a href="https://www.dropbox.com/s/hrqtpv9fci5cuga/Book1.xls?dl=0" rel="noreferrer">Sample File</a>. But not all files have same structure.</p>