Export Sheet1 as CSV without trailing commas or empty rows that are just commas at the end of the CSV file

[SWAY14]

Hello Everyone,
First time user here. I am looking for help with exporting sheet 1 as a CSV file but also getting ride of the empty rows that represented by a bunch of commas when I open the csv file with textedit or notepad.
I have tried numerous online codes but none seem to do the trick.

 

[DanteAmor]

Hi and welcome to MrExcel!

Assuming the data starts in cell A1.
The csv file is saved in the same folder where you have the excel with the macro.

Try this:

Sub Export_CSV()
  Dim h1 As Worksheet
  Dim fPath As String, fName As String, cad As String
  Dim lr As Long, lc As Long, i As Long
  Dim c As Range

  Set h1 = Sheets("Sheet1")
  fPath = ActiveWorkbook.Path & "\"
  If Left(fPath, 1) <> "\" Then fPath = fPath & "\"
  fName = "filename.csv"
  '
  lr = h1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  lc = h1.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
  Open fPath & fName For Output As #1
  For i = 1 To lr
    If WorksheetFunction.CountA(h1.Range(h1.Cells(i, "A"), h1.Cells(i, lc))) > 0 Then
      For Each c In h1.Range(h1.Cells(i, "A"), h1.Cells(i, lc))
        cad = cad & c.Value & ","
      Next
      If cad <> "" Then cad = Left(cad, Len(cad) - 1)
      Print #1, cad
      cad = Empty
    End If
  Next
  Close #1
End Sub

 

[SWAY14]

Hi and welcome to MrExcel!

Assuming the data starts in cell A1.
The csv file is saved in the same folder where you have the excel with the macro.

Try this:

Sub Export_CSV()
  Dim h1 As Worksheet
  Dim fPath As String, fName As String, cad As String
  Dim lr As Long, lc As Long, i As Long
  Dim c As Range

  Set h1 = Sheets("Sheet1")
  fPath = ActiveWorkbook.Path & "\"
  If Left(fPath, 1) <> "\" Then fPath = fPath & "\"
  fName = "filename.csv"
  '
  lr = h1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  lc = h1.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
  Open fPath & fName For Output As #1
  For i = 1 To lr
    If WorksheetFunction.CountA(h1.Range(h1.Cells(i, "A"), h1.Cells(i, lc))) > 0 Then
      For Each c In h1.Range(h1.Cells(i, "A"), h1.Cells(i, lc))
        cad = cad & c.Value & ","
      Next
      If cad <> "" Then cad = Left(cad, Len(cad) - 1)
      Print #1, cad
      cad = Empty
    End If
  Next
  Close #1
End Sub

Thank you very much for your help I finally figured it out

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.