Do you mean the code in Message #3 above? If so, I did not need to do that when I tested the code before posting it.
Rick,
Any suggestion as to why when I use .CurrentRegion, my code works fine
......................
Pete
.......
. --: Using your second code it will work with empty cells as well
With Sheets("Sheet1").[COLOR=#FF0000][B]Range("A4:A87")[/B] [/COLOR]
FileText = FileText & vbCrLf & Join(Application.Index(.Rows(X).Value, 1, 0), "|")
That's right - because this code is designed to delimit columns of data with a "|" character, it stands to reason that there needs to be at least two columns of data.
I wish I'd realised that earlier, though!
I am sorry, but my answer in Message #9 has led this thread astray. If all you want to do is concatenate a single column of contiguous cells together to form a pipe delimited text string, you can do that with one line of code...Both Answers:
Code:With Sheets("Sheet1").[COLOR=#ff0000][B]Range("A4:A87")[/B] [/COLOR]
and
Replace A1 with A4 give me a:
Run Time Error: '13':
Type mismatch
It's that man again!I am sorry, but my answer in Message #9 has led this thread astray. If all you want to do is concatenate a single column of contiguous cells together to form a pipe delimited text string, you can do that with one line of code...
PipeDelimitedText = Join(Application.Transpose(Sheets("Sheet1").Range("A4:A87").Value), "|")