For Col = 1 To 26
PipeDelimitedText = PipeDelimitedText & "|" & Join(Application.Transpose(Sheets( _
"Sheet3").Range("A1:A20").Offset(, Col - 1).Value), "|")
Next
What exactly do you mean by Row 2+3+4(wrapped)? Do you mean merged so that they appear to be a single cell with the text wrapped within that merged cell?Keep the first row as is and wrap the other rows below it
Row 1: Hi my name is
Row 2+3+4 (wrapped): The best person ever is 79222 miles away from a bacon sandwich and a frozen anteater walking on clouds
What exactly do you mean by Row 2+3+4(wrapped)? Do you mean merged so that they appear to be a single cell with the text wrapped within that merged cell?
Sorry, but I am still not clear on this. Let's say this is what your worksheet looks like...Yes, basically. I just need the rows (not including the first row) to display merged in a text file (Notepad) when its pasted there. Hope that makes sense.
A | B | C | |
1 | Hi my name is | One two | |
2 | The best person ever | Three four five | |
3 | is 79222 miles away | six seven | |
4 | from a bacon sandwich. | eight nine ten. | |
5 |
Sorry, but I am still not clear on this. Let's say this is what your worksheet looks like...
A
B
C
1
Hi my name is
One two
2
The best person ever
Three four five
3
is 79222 miles away
six seven
4
from a bacon sandwich.
eight nine ten.
5
<TBODY>
</TBODY>
Show me how that should look in the text file that you are going to load into Notepad.
Just to clarify... will that be one single line or two separate lines in the text file?This is what I'm looking for:
Hi my name is|One two|
The best person ever|Three four five|is 79222 miles away|six seven|from a bacon sandwich.|eight nine ten.|
Just to clarify... will that be one single line or two separate lines in the text file?
Two seprate lines. The first line (first row in Excel) will always be its own line, and the next line will be one long line depending on how many rows there are from Excel.
Sub PipeDelimitedRowJoins()
Dim R As Long, C As Long, LastRow As Long, FileNo As Long
Dim FileName As String, PipeDelimitedText As String
Const ColCount As Long = [B][COLOR=#ff0000]26
[/COLOR][/B] FileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\[COLOR=#0000FF][B]Testfile.txt[/B][/COLOR]"
LastRow = Columns("[COLOR=#008000][B]A[/B][/COLOR]").Resize(, ColCount).Find("*", , xlValues, , xlRows, xlPrevious).Row
PipeDelimitedText = Join(Application.Index(Range("[B][COLOR=#008000]A1[/COLOR][/B]").Resize(, ColCount).Value, 1, 0), "|") & "|" & vbCrLf
For R = [COLOR=#800080][B]2[/B][/COLOR] To LastRow
PipeDelimitedText = PipeDelimitedText & Join(Application.Index(Cells(R, "[COLOR=#008000][B]A[/B][/COLOR]").Resize(, ColCount).Value, 1, 0), "|") & "|"
Next
FileNo = FreeFile
Open FileName For Output As #FileNo
Print #FileNo, PipeDelimitedText
Close #FileNo
End Sub