Code modification to not export empty values (as zero) and empty range

pedexe90

Board Regular
Joined
Apr 18, 2018
Messages
59
I have the below VBA that allows me to export a CSV file from a defined range. However, there are 2 issues here that I need your help to resolve:

1) When I export using this code, the exported CSV file includes empty/blank fields from the defined range. I believe the problem is that the .csv is taking the cells that have a formula or are within the range and evaluates them as empty strings.

2) When I export the file, zero values (where the formula is not returning any values) are being shown as zero "0" in the CSV file. I'd like these fields to be completely blank.

In other words, I want all empty areas to be shown as blank fields.

Really appreciate your help.

VBA Code:
Sub saveRangeToCSV()

    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range

    Application.DisplayAlerts = False
    On Error GoTo err

    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "CSV-Dataloader-Import-File-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"

    Set rngToSave = Range("A3:AE2000")
    rngToSave.Copy

    Set tempWB = Application.Workbooks.Add(1)
    With tempWB
        .Sheets(1).Range("A1").PasteSpecial xlPasteValues
        .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSVUTF8, CreateBackup:=False
        .Close
    End With
err:
    Application.DisplayAlerts = True
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,096
Messages
5,852,153
Members
431,488
Latest member
ePayslip

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
Top