Export .csv and ignore blank rows

mattdavid.hall

New Member
Joined
Oct 2, 2008
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:

EmptyStrings.png


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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
VBA Code:
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
 
Upvote 0
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.
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top