VBA / Macros to copy & paste and create a .CSV file, but to ignore keep empty values and not paste zero

[maleodillet]

Hi Gurus,

I have this VBA Code that I using to copy an Excel range and then use it to create a CSV file. It is using "PasteValuesOnly" to get the Job done but when a cell from the range is empty it pastes a Zero when I actually want it to keep the empty cell on the newly created CSV file. Any suggestions? please see my code below:

---

Sub CreateDatafile()


Dim content As String
Dim rng As Range
Set rng = Range("Q20:Y40")
Dim Path As String
Dim FileName1 As String


Dim sWB As Workbook, _
sWS As Worksheet

Dim dWB As Workbook, _
dWS As Worksheet

Path = "C:\Users\maleo\Desktop\MASTERDATA_LOADING"
'Path = "\ROOT\WEBFOLDERS\PLNSBX\SUMMARY\DATAMANAGER\DATAFILES\ or C:\Users\maleo\Desktop\MASTERDATA_LOADING"
FileName1 = Range("R16")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet


Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)


sWS.Range("Q20:Y40").Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWB.SaveAs Filename:=Path & FileName1 & ".csv", FileFormat:=xlCSV
dWB.Close False

MsgBox "The Updated Master Data file has been created"

End Sub

---

 

[Joe4]

Welcome to the Board!

but when a cell from the range is empty it pastes a Zero when I actually want it to keep the empty cell on the newly created CSV file.

I cannot reproduce this behavior.
What exactly is in these blanks cells? Are they really blank, or is there a formula that is returning blanks?
Are you sure that there really aren't any zeroes in the original data that may just be hidden from your view with a Custom Format or Conditional Formatting?
How exactly are you viewing the CSV file after it is created to confirm that there are zeroes in it?

 

[Fluff]

Hi & welcome to the board
Try

Set dWB = Workbooks.Add
Set dWs = dWB.Sheets(1)

dWs.Range("A1:I21").Value = sWs.Range("Q20:Y40").Value
dWB.SaveAs Filename:=Path & Filename1 & ".csv", FileFormat:=xlCSV
dWB.Close False

 

[maleodillet]

Welcome to the Board!

I cannot reproduce this behavior.
What exactly is in these blanks cells? Are they really blank, or is there a formula that is returning blanks?
Are you sure that there really aren't any zeroes in the original data that may just be hidden from your view with a Custom Format or Conditional Formatting?
How exactly are you viewing the CSV file after it is created to confirm that there are zeroes in it?

Joe, Actually you're right. the Zero Value it's hidden by formatting so i guess I have to figure out a way make that cell "empty if equal to zero" prior to creating the copying the values and creating the CSV right?

 

[Joe4]

You can do that with a simple Find/Replace after you paste it in your new sheet:

    [COLOR=#333333]dWs.Range("A1:I21")[/COLOR].Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[maleodillet]

thanks for the response, but wouldn't this replace any other zeros for other cells such as "2018" being replaced with "2 18" and so on?

 

[Joe4]

thanks for the response, but wouldn't this replace any other zeros for other cells such as "2018" being replaced with "2 18" and so on?

Nope. Test it out and see!

The key is this part:

LookAt:=xlWhole

If you do Find/Replace manually, this is the "Match entire cell contents" check box.
This tells it to only replace if the entire entry matches exactly. So it will only replace cells that are EXACTLY 0 (and will not touch the others than might have a zero in them, among other things).

 

[maleodillet]

Amazing Thanks.