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

 

[Jorgi]

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.

I'm not sure what else can be wrong with my code sorry as I said I'm not the expert I'm more very beginner

 

[Alex Blakenburg]

Try
Changing this:-

Set rngToSave = Range("A1:G1", rFound)

To this:

Set rngToSave = Range("A1:G" & rngToSave.Row)

 

[jasonb75]

The reason it doesn't work is because of this line. The first one is from my post, the second one is from your code. You have changed the name at the start of the line which is the reason for it failing.

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

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

When I looked at it originally, I had checked to see if you made the change in the right place, which you had. I didn't notice that the variable name was wrong.

 

[Jorgi]

The reason it doesn't work is because of this line. The first one is from my post, the second one is from your code. You have changed the name at the start of the line which is the reason for it failing.

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

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

When I looked at it originally, I had checked to see if you made the change in the right place, which you had. I didn't notice that the variable name was wrong.

jasonb75 I have amended the code again and it works. Thank you very much for your help