Outputting a sheet to .csv with special formatting... (vba)

[RobbieC]

Hi there, I have a sheet which I need to output to .csv (or more specifically .txt)

I've posted the workbook to my webserver: http://www.bluecustard.co.uk/Book1.xlsm

Sheet 1 (DATA_FULL) holds the data which I need to export. I can do this using:

Sub ExportRange()
Dim FirstCol As Integer
Dim LastCol As Integer
Dim C As Integer
Dim FirstRow
Dim LastRow
Dim R
Dim data
Dim ExpRng As Range
Sheets("DATA_FULL").Range("A1").Select
    Set ExpRng = ActiveCell.CurrentRegion
    FirstCol = ExpRng.Columns(1).Column
    LastCol = FirstCol + ExpRng.Columns.Count - 1
    FirstRow = ExpRng.Rows(1).Row
    LastRow = FirstRow + ExpRng.Rows.Count - 1


    'Open ThisWorkbook.Path & "\textfile.csv" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  ' csv file
    Open ThisWorkbook.Path & "\textfile.txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  '''''or txt file
        For R = FirstRow To LastRow
            For C = FirstCol To LastCol
                data = ExpRng.Cells(R, C).Value
                If data = "" Then data = ""
                If IsNumeric(data) Then data = Val(data)
                If C <> LastCol Then
                    Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , data;
                Else
                    Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , data
                End If
            Next C
        Next R
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub

The sheet is broken up into 18 sections and each section has 4 lines of 'headings' and then the subsequent data. Also, each 'block/section/ is separated by a blank row..

The code above outputs the entire sheet and replaces any 'space' with "",

However, my problem is that I need to format the output so that a blank row just outputs a blank row in the .txt file and not:
"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""

Secondly, it is outputting each section to the max number of columns, and I need to tailor each section to the number of columns per section. ie:

"GROUP","PROJ","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""
"HEADING","PROJ_ID","PROJ_NAME","PROJ_LOC","PROJ_CLNT","PROJ_CONT","PROJ_ENG","PROJ_MEMO","FILE_FSET","","","","","","","","","","","","","","","","","","","","","","","","","","",""
"UNIT","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""
"TYPE","ID","X","X","X","X","X","X","X","","","","","","","","","","","","","","","","","","","","","","","","","","",""
"DATA","123456","Newmarket","Newmarket","ACME Engineering","ACME Engineering","ACME Engineering","AGS DATA","","","","","","","","","","","","","","","","","","","","","","","","","","","",""
"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","" <- blank line

needs to be outputted as:

"GROUP","PROJ" '<- note this line is only 2 columns with no "","" - the rest span the max column
"HEADING","PROJ_ID","PROJ_NAME","PROJ_LOC","PROJ_CLNT","PROJ_CONT","PROJ_ENG","PROJ_MEMO","FILE_FSET"
"UNIT","","","","","","","",""
"TYPE","ID","X","X","X","X","X","X","X"
"DATA","123456","Newmarket","Newmarket","ACME Engineering","ACME Engineering","ACME Engineering","AGS DATA",""
this line is a space with no "",""

In my workbook, I have each section broken down into separate sheets. Perhaps the .txt file could be built up bit by bit so that it conforms to the above format?

I've searched everywhere for a possible solution, but have drawn a blank.

If you can help me out with my problem, or push me in the right direction, I'd be very grateful

Thanks very much!