Export .csv and ignore blank rows

[mattdavid.hall]

We have a script where we export a specific sheet to a new .csv. The problem is that the .csv is taking the cells that have a formula and evaluating it as an empty string.

Here is our script:

Public Sub ExportWorksheetAndSaveAsCSV()

Dim wbkExport As Workbook
Dim shtToExport As Worksheet

Set shtToExport = ThisWorkbook.Worksheets("UploadTemplate")     'Sheet to export as CSV
Set wbkExport = Application.Workbooks.Add
xStrDate = Format(Now, "yyyy-mm-dd hh-mm-ss")
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False                       'Possibly overwrite without asking
wbkExport.SaveAs Filename:="L:\Human Resources\Y drive files\ADPUpload - " & xStrDate & ".csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

End Sub

Here is what it looks like when we open our file:

The system we are uploading to does not like the empty rows shown in the red box. The sheet can have anywhere from rows 40-200 populated each week, so we copied our formula down to row 200, and the empty rows are what we want to not export.

Is there a way to modify our script to ignore rows in the sheet that have a formula but have no real data when exporting?

 

[bstory84]

I believe it comes down to what is considered your USED RANGE. Since those cells contain values or formulas it becomes apart of the CSV export because it is USED or occupied.
My suggestion is to Copy & Paste as values to remove the formulas then remove the Empty rows as well. If this sounds ok.. . Then try the script below.

Public Sub ExportWorksheetAndSaveAsCSV()

Dim wbkExport As Workbook
Dim shtToExport As Worksheet

Set shtToExport = ThisWorkbook.Worksheets("UploadTemplate")     'Sheet to export as CSV
Set wbkExport = Application.Workbooks.Add
xStrDate = Format(Now, "yyyy-mm-dd hh-mm-ss")
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False                       'Possibly overwrite without asking
'-----Added script
    With wbkExport.Worksheets("UploadTemplate")
    'Paste values - remove formulas
        .Activate
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    'Delete Empty rows
        Dim LastRowIndex As Integer
        Dim RowIndex As Integer
        Dim UsedRng As Range
        Set UsedRng = ActiveSheet.UsedRange
        LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
        For RowIndex = LastRowIndex To 1 Step -1
            If Application.CountA(Rows(RowIndex)) = 0 Then
                Rows(RowIndex).Delete
            End If
        Next RowIndex
    End With
'-------Back to original script
wbkExport.SaveAs Filename:="L:\Human Resources\Y drive files\ADPUpload - " & xStrDate & ".csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

End Sub

 

[mattdavid.hall]

I believe it comes down to what is considered your USED RANGE. Since those cells contain values or formulas it becomes apart of the CSV export because it is USED or occupied.
My suggestion is to Copy & Paste as values to remove the formulas then remove the Empty rows as well. If this sounds ok.. . Then try the script below.

Public Sub ExportWorksheetAndSaveAsCSV()

Dim wbkExport As Workbook
Dim shtToExport As Worksheet

Set shtToExport = ThisWorkbook.Worksheets("UploadTemplate")     'Sheet to export as CSV
Set wbkExport = Application.Workbooks.Add
xStrDate = Format(Now, "yyyy-mm-dd hh-mm-ss")
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False                       'Possibly overwrite without asking
'-----Added script
    With wbkExport.Worksheets("UploadTemplate")
    'Paste values - remove formulas
        .Activate
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    'Delete Empty rows
        Dim LastRowIndex As Integer
        Dim RowIndex As Integer
        Dim UsedRng As Range
        Set UsedRng = ActiveSheet.UsedRange
        LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
        For RowIndex = LastRowIndex To 1 Step -1
            If Application.CountA(Rows(RowIndex)) = 0 Then
                Rows(RowIndex).Delete
            End If
        Next RowIndex
    End With
'-------Back to original script
wbkExport.SaveAs Filename:="L:\Human Resources\Y drive files\ADPUpload - " & xStrDate & ".csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

End Sub

Thank you for the suggestion! I'm sorry for taking so long to get to this, I was pulled off on to another project so I'm just getting back to this topic.

Unfortunately the script you provided does not work. When we open the .csv is notepad it still has all of the blank rows from the formulas. It takes a long time to run also (60+ seconds). I do think the method of copying and then pasting the values is the correct way to do it though.

Thanks again! We'll keep looking for ways to get this done.

 

[bstory84]

Based on the sample data provided(which I know is limited), it worked for me. Does the VBA error? or just not the desired result?

I would say that. . .
1. You do need to remove formulas since they are occupied and therefore will have empty values in your CSV.
2. Having formulas does not equate to an empty cell. So you would not be able to delete the empty row.

 

[Marc L]

We'll keep looking for ways to get this done

Link at least a workbook sample and its expected csv text file on a files host website like Dropbox …​

 

[Shinoy98]

Any update on this issue?