Concate multiple columns in VBA and save output to file

[Akhilesh28]

Hello guys,
This us my 1st post.
Please do the needful

I am trying to concatenate 2 columns out of 7 and save those columns data in a file seperated by comma.
E.g Cell A2 has abc and Cell E2 has 123.
A3 has pqr and E3 has 789

Expected Result:
abc,123
pqr,789

But I am getting
"abc,123
","pqr,789
",


Code:

Private sub export()
Dim myfile as string, count as long, cellvalue as variant, i as integer

Myfile = location of file

Count= worksheetfunction.CountA("A1", range("a1").end(xldown))

Open Myfile for output as #1

For i =2 to count
Cellvalue = rng.cells(i,1).Value & ','& rng.cells(i,5).Value & vbCrLf
Write #1, cellvalue
Next i
Close #1

End sub

Please ignore the cases. I typed in bit hurry

 

[Marc L]

Hi,​

​

that is normal just reading the VBA help of Write # statement ! So use Print # instead …​

 

[Akhilesh28]

Hi,​

​

that is normal just reading the VBA help of Write # statement ! So use Print # instead …​

Thanks.. it worked. But 1 othe issue I got. It is adding 1 extra blank line at end of file. Is it possible some how we can remove the last blank line? It looks like blank string is present

Abc123
Pqr789

 

[Rick Rothstein]

Here is a non-looping method to do what you asked for (and there is no blank line at the end of the file)...

Sub Export()
  Dim LastRow As Long, MyFile As String
  MyFile = Location Of File
  LastRow = Range("A1").End(xlDown).Row
  Open MyFile For Output As #1
  Print #1, Join(Evaluate(Replace("TRANSPOSE(A2:A#&"", ""&E2:E#)", "#", LastRow)), vbNewLine);
  Close #1
End Sub

If you want to stick with your code as posted (but using Print instead of Write, of course), you would change that code line to this...

Print #1, cellvalue & IIf(i = Count, ";", "")

I am pretty sure that my code will be faster than yours though.