Adjust CSV Output to Remove Xtra Commas

[steve2115]

Need help modifying the below code to meet my requirement. Any help would be greatly appreciated. I have 100's of files to convert.

Option Explicit
 
Sub CreateCIF()
Dim ws As Worksheet
Dim rng As Range
Dim strFileName As String
Dim FF As Long
Dim NoVals As Long
Dim arrVals
    Set ws = Worksheets("Sheet1")
    Set rng = Range("A1")
    
    strFileName = ws.Name & ".cif"
    
    FF = FreeFile()
    
    Open "C:\Users\skeebaug\Desktop\" & strFileName For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 
    
        While rng.Value <> ""
            
            NoVals = Application.WorksheetFunction.CountA(rng.EntireRow)
            
            If NoVals = 1 Then
                Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , rng.Value
            Else
                arrVals = rng.Resize(, NoVals).Value
                arrVals = Application.WorksheetFunction.Transpose(arrVals)
                arrVals = Application.WorksheetFunction.Transpose(arrVals)
                Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , Join(arrVals, ",")
            End If
            
            Set rng = rng.Offset(1)
        Wend
        
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 
    
End Sub

Here is the layout of data in excel. In this example there are only 2 rows of "catalog data" (rows 13&14), however other catalogs will have more

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

CIF_I_V3.0

2

CHARSET:

UTF-8

3

LOADMODE:

F

4

CODEFORMAT:

UNSPSC_V13.5

5

CURRENCY:

USD

6

SUPPLIERID_DOMAIN:

NetworkID

7

ITEMCOUNT:

2

8

TIMESTAMP:

8/4/2017

9

UNUOM:

TRUE

10

COMMENTS:

992966_8552_VIBRATION ANALYSTS INC_060217

11

FIELDNAMES: Supplier ID

Supplier Part ID

Manufacturer Part ID

Item Description

SPSC Code

Unit Price

Unit of Measure

Lead Time

Manufacturer Name

Supplier URL

Manufacturer URL

Market Price

Supplier Part Auxiliary ID

Language

Currency

Short Name

Image

Thumbnail

UNSPSC

MinimumQuantity

QuantityInterval

SimilarItems

Effective Date

Expiration Date

Keywords

12

DATA

13

VAI001

VAI001

Vibration Analysis

41113320

10

EA

5

Vibration Analysts, Inc.

USD

Vibration Analysis

41113320

1

1

Vibration

14

VAI002

VAI002

Vibration Equipment Fee

41113320

81

EA

5

Vibration Analysts, Inc.

USD

Vibration Equipment Fee

41113320

1

1

Vibration

15

ENDOFDATA

<tbody>
</tbody>

Here is the current output results of code.

CIF_I_V3.0
CHARSET:,UTF-8
LOADMODE:,F
CODEFORMAT:,UNSPSC_V13.5
CURRENCY:,USD
SUPPLIERID_DOMAIN:,NetworkID
ITEMCOUNT:,2
TIMESTAMP:,8/4/2017
UNUOM:,True
COMMENTS:,992966_8552_VIBRATION ANALYSTS INC_060217
FIELDNAMES: Supplier ID,Supplier Part ID,Manufacturer Part ID,Item Description,SPSC Code,Unit Price,Unit of Measure,Lead Time,Manufacturer Name,Supplier URL,Manufacturer URL,Market Price,Supplier Part Auxiliary ID,Language,Currency,Short Name,Image ,Thumbnail,UNSPSC,MinimumQuantity,QuantityInterval,SimilarItems, Effective Date, Expiration Date,Keywords
DATA

Here is the EXPECTED results

CIF_I_V3.0
CHARSET:UTF-8
LOADMODE:F
CODEFORMAT:UNSPSC_V13.5
CURRENCY:USD
SUPPLIERID_DOMAIN:NetworkID
ITEMCOUNT:2
TIMESTAMP:8/4/2017
UNUOM:TRUE
COMMENTS:992966_8552_VIBRATION ANALYSTS INC_060217
FIELDNAMES: Supplier ID,Supplier Part ID,Manufacturer Part ID,Item Description,SPSC Code,Unit Price,Unit of Measure,Lead Time,Manufacturer Name,Supplier URL,Manufacturer URL,Market Price,Supplier Part Auxiliary ID,Language,Currency,Short Name,Image ,Thumbnail,UNSPSC,MinimumQuantity,QuantityInterval,SimilarItems, Effective Date, Expiration Date,Keywords
DATA
,VAI001,VAI001,Vibration Analysis,41113320,29,EA,5,"Vibration Analysts, Inc.",,,,,,USD,Vibration Analysis,Image Url,,41113320,1,1,,,,Vibration
,VAI002,VAI002,Vibration Equipment Fee,41113320,81.91,EA,5,"Vibration Analysts, Inc.",,,,,,USD,Vibration Equipment Fee,,,41113320,1,1,,,,Vibration
ENDOFDATA

Need code to also remove the leading comma right after each of the header fields


CHARSET:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]UT[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]F-8
LOADMODE:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]F
CODEFORMAT:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]U[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]NSPSC_V13.5
CURRENCY:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]USD
SUPPLIERID_DOMAIN:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]N[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]etworkID
ITEMCOUNT:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]2
TIMESTAMP:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]8[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]/4/2017
UNUOM:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]True
COMMENTS:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]92966_8552_VIBRATION ANALYSTS INC_060217

Code is also removing the all data below "DATA" (cell A12 on excel table). Need this to stay.
ENDOFDATA should be the last line of text with no trailing commas.