Fastest method to remove Empty rows and Columns From Excel Files using Interop
<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>