Remove Blank Rows in excel using vbscript
<p>We get excel file from source system and we need to convert it to CSV. 
Sometimes, last line of excel file we get empty rows. We need to delete that row before the file is placed as CSV. I use the below code for doing this conversion but it doesn’t work all the time.It still retains that empty rows which causes problem when converted to CSV and last line of the CSV just shows commas.</p>

<p>Below is the script</p>

<pre><code>Dim StrSourcePathFile, StrDestPathFile

set xlApp = CreateObject("excel.application")
Set fso = CreateObject("Scripting.FileSystemObject")

StrSourcePathFile = "c:\temp\var31.XLSX"

StrDestPathFile = "c:\temp\var31.csv"

xlApp.Workbooks.Open StrSourcePathFile
xlApp.DisplayAlerts = False

If xlApp.ActiveSheet.Rows(1).Text = "" Then
xlApp.ActiveSheet.Rows(1).EntireRow.Delete
End If


If xlApp.ActiveSheet.Rows((xlApp.ActiveSheet.UsedRange.Rows.Count)).Text = "" Then
xlApp.ActiveSheet.Rows((xlApp.ActiveSheet.UsedRange.Rows.Count)).EntireRow.Delete
End If 

xlApp.ActiveWorkbook.SaveAs StrDestPathFile,6

xlApp.ActiveWorkbook.Saved = true


xlapp.Quit()


Set fso = Nothing
Set xlApp = Nothing
</code></pre>

<p>Thanks in advance.</p>