Converting Excel data to notepad

Question

Whenever I run the macro text file is generated but there is space after every line., How do I fix it?

Here it is my code

Sub test()
    Dim txt As String, i As Long, rng As Range
    Set rng = Range("A1:C500")
    For i = 1 To rng.Rows.Count
        With WorksheetFunction
            txt = txt & vbCrLf & Join(.Transpose(.Transpose(rng.Rows(i).Value)), vbTab)
        End With
    Next
    Open "c:\test\test.txt" For Output As #1
        Print #1, Mid$(txt, 2)
    Close #1

End Sub

Answer 1

Try Triming the text. The code should be something like this:

txt = txt & vbCrLf & Trim(Join(.Transpose(.Transpose(rng.Rows(i).Value)), vbTab))