VBA save data as CSV without empty "" - empty as result of formula ""

[Jorgi]

Hello, Could you be so kind and help me to save CSV via VBA without empty rows result of formula "". I need to save the header and 7 columns, data as results of formula will be only in one column. If there is no data as result of formula but only "" as result of formula CSV has ,,,,,,, and I would really want to get rid of ,,,,,,, from the CSV file. Thank you for your help.

Current outcome of the macro when CSV is saved

The Aim

The 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-Exported-File-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"

Set rngToSave = Range("A1:G20")
rngToSave.Copy

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

 

[jasonb75]

You can't change how it is saved, the simple fix is to change the range that is copied so that it doesn't include the blank rows.

Dim rFound As Range, rngToSave As Range
Set rFound = Range("A1:G20").Find("?*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Set rngToSave = Range("A1:G1", rFound)
rngToSave.Copy

 

[Jorgi]

You can't change how it is saved, the simple fix is to change the range that is copied so that it doesn't include the blank rows.

Dim rFound As Range, rngToSave As Range
Set rFound = Range("A1:G20").Find("?*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Set rngToSave = Range("A1:G1", rFound)
rngToSave.Copy

Thank you jasonb75 could let me know if the code below is correct, please? If is not correct can you show me how the whole code should look like. Thank you

Sub saveRangeToCSV()

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

Application.DisplayAlerts = False
On Error GoTo err

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

Set rngToSave = Range("A1:G20").Find("?*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Set rngToSave = Range("A1:G1", rFound)
rngToSave.Copy

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

 

[jasonb75]

could let me know if the code below is correct

Why not just run the code and see if it works?

If you want me to review your code, please format it correctly.

 

[Jorgi]

Why not just run the code and see if it works?

If you want me to review your code, please format it correctly.

I have ran the code with your input and it doesn't work. The code I sent in my first post is not my code found it online. I'm not VBA expert so more likely I modified the code with your suggestions incorrectly.

 

[jasonb75]

I have ran the code with your input and it doesn't work.

You're going to need to be a little bit more specific than just saying it doesn't work.
Does it run but nothing happens, Is it doing something but not what it should be doing, runtime error, something else?

At a glance your edit looks correct, but as I said, if you want me to review it then please format it properly.

 

[Jorgi]

You're going to need to be a little bit more specific than just saying it doesn't work.
Does it run but nothing happens, Is it doing something but not what it should be doing, runtime error, something else?

At a glance your edit looks correct, but as I said, if you want me to review it then please format it properly.

When I run the macro nothing is happening, no CSV file created no errors just nothing. What you mean by format it properly?

 

[jasonb75]

What you mean by format it properly?

When you post code, you should click on the </> icon and paste it into the popup window.

I just checked it again, what you've added to your code is not the same as what I suggested. Check your edit carefully and you should find your mistake.

 

[Jorgi]

Ok thank you for the explanation

Sub saveRangeToCSV()

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

Application.DisplayAlerts = False
On Error GoTo err

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

Set rngToSave = Range("A1:G20").Find("?*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Set rngToSave = Range("A1:G20", rFound)
rngToSave.Copy

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

 

[Jorgi]

Ok thank you for the explanation

Sub saveRangeToCSV()

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

Application.DisplayAlerts = False
On Error GoTo err

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

Set rngToSave = Range("A1:G20").Find("?*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
Set rngToSave = Range("A1:G20", rFound)
rngToSave.Copy

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

I'm not sure what else can be wrong with this code ?