Thanks Dick for the suggestion.

I had already tried to modify the piece of code from your site but I
end up with all the information in the first column - essentially the
data is in 1d not 2d.

Here is what I had tried - any suggestions?

Sub test_RNG2CSV_Daily()
Dim sFilename As String

sFilename = "C:\Documents\Personal\CV\db\Out_1.csv"
RNG2CSV_Daily sFilename

End Sub
Sub RNG2CSV_Daily(sFilename As String)
Dim rCell As Range
Dim rRow As Range
Dim vaColPad As Variant
Dim i As Long
Dim sOutput As String
Dim sFname As String, lFnum As Long

'Required width of columns
vaColPad = Array(0, 0, 0, 0, 4)
i = LBound(vaColPad)

'Open a text file to write
sFname = "C:\Documents\Personal\CV\db\Out_2.csv"
lFnum = FreeFile

Open sFname For Output As lFnum
'Dim rRange As Range: Set rRange = Worksheets("CV List -
v1.0").UsedRange.Rows
Dim rRange As Range: Set rRange = Selection
'Loop through the rows
' For Each rRow In Worksheets("Sheet3").UsedRange.Rows
For Each rRow In rRange
'Loop through the cells in the rows

For Each rCell In rRow.Cells
'If the cell value is less than required, then pad
'it with zeros, else just use the cell value
If Len(rCell.Value) < vaColPad(i) Then
sOutput = sOutput & Application.Rept(0, _
vaColPad(i) -
Len(rCell.Value)) & rCell.Value & ","
Else
sOutput = sOutput & rCell.Value & ","
End If
i = i + 1
Next rCell
'remove the last comma
sOutput = Left(sOutput, Len(sOutput) - 1)

'write to the file and reinitialize the variables
Print #lFnum, sOutput
sOutput = ""
i = LBound(vaColPad)
Next rRow

'Close the file
Close lFnum
End Sub