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

Jorgi

New Member
Joined
Jul 7, 2021
Messages
41
Office Version
  1. 2019
Platform
  1. Windows
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.


1637400857109.png


Current outcome of the macro when CSV is saved
1637400423564.png

The Aim
1637400385525.png

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
 

Attachments

  • 1637400283903.png
    1637400283903.png
    6.7 KB · Views: 6
  • 1637400319971.png
    1637400319971.png
    7 KB · Views: 6

Jorgi

New Member
Joined
Jul 7, 2021
Messages
41
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
Try
Changing this:-
VBA Code:
Set rngToSave = Range("A1:G1", rFound)

To this:
VBA Code:
Set rngToSave = Range("A1:G" & rngToSave.Row)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
Set rFound = Range("A1:G20").Find("?*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
VBA Code:
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

New Member
Joined
Jul 7, 2021
Messages
41
Office Version
  1. 2019
Platform
  1. Windows
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.
VBA Code:
Set rFound = Range("A1:G20").Find("?*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False)
VBA Code:
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 :)
 
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,163,479
Messages
5,831,915
Members
430,091
Latest member
Generally_confused

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