VBA Save CSV with headers and data only from one column as result of formula

[Jorgi]

Hello, Could you be so kind and help me with VBA, please? I would like to save the below example as CSV via marco. There will be only data in column C as result of formula but the header data needs to be in separated columns A B C D on csv also if there will be no data e.g. C5 the result of a formula will be "" and will be great if will be possible not to include "" on the csv/txt. The CSV will also need to be saved in the same folder what xlsx (the working on workbook). I have managed to get macro to save only column C ( Header C1 and data from C2,C3,C4 and C5 with "" which is not ideal) with data but without headers from A1 B1 and D1. Thank you so much for you help and guidance.

Sub saveABCDToCSV()

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 & "\" & "ABCD-" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & ".csv"

Set rngToSave = Range("C1:C5")
rngToSave.Copy

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

 

[Dermot]

Try this

Sub SaveAsCSV()
  Dim C, i, myCSVFileName
  C = Cells(1, 3).Resize(Cells(1, 3).End(xlDown).Row, 1) 'read column into memory
  myCSVFileName = ActiveWorkbook.Path & "\" & "ABCD-" & Format(Now, "dd-MMM-yyyy") & ".csv"
  Open myCSVFileName For Output As #1
  For i = LBound(C) To UBound(C) 'write line by line
    Print #1, ",," & C(i, 1) & ","
  Next i
  Close #1
End Sub

 

[Jorgi]

Try this

Sub SaveAsCSV()
  Dim C, i, myCSVFileName
  C = Cells(1, 3).Resize(Cells(1, 3).End(xlDown).Row, 1) 'read column into memory
  myCSVFileName = ActiveWorkbook.Path & "\" & "ABCD-" & Format(Now, "dd-MMM-yyyy") & ".csv"
  Open myCSVFileName For Output As #1
  For i = LBound(C) To UBound(C) 'write line by line
    Print #1, ",," & C(i, 1) & ","
  Next i
  Close #1
End Sub

Thank you Dermot for the code. I probably did something wrong. I'm not very good with VBA still learning process. Thank you for any hints

 

[Fluff]

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

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.