I have a column with about 6,000 rows.
I need a macro that will combine cells in the column with a pipe(|) between
each.
The key is that each row should be no more than 1,000 characters.

You can modify the macro he

http://www.mcgimpsey.com/excel/mergedata.html

change

vTxtArr(i, 1) = vTxtArr(i, 1) & sDelim & vTxtArr(i, j)

to

vTxtArr(i, 1) = Left(vTxtArr(i, 1) & sDelim & vTxtArr(i, j), 1000)


In article ,
Bob wrote:

I have a column with about 6,000 rows.
I need a macro that will combine cells in the column with a pipe(|) between
each.
The key is that each row should be no more than 1,000 characters.

Since in cell max. no. of characters can be 32767 you cant put the
result in to a cell.

You can however put the result in to a text file.
See code below for the same, assuming the column is 'F'. You can change
the range as your requirement.
(With a P4 Machine it takes almost 6 seconds to write to the text file,
and the text file size would be around 60 MB if 1000 character per
cell.)

In the code the text file must be opened for 'Append' (8).
(-1) indicates in Unicode format.

The result is put in to C:\ drive in file combofile.txt.
You need not create the file first the write command will create it, if
it does not exist.


Sub combiner()
Dim myRange As Range, fs, f

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("c:\combofile.txt", 8, -1)
Set myRange = Worksheets("Sheet1").Range("F1:F6000")

For Each c In myRange.Cells
f.Write Worksheets("Sheet1").Range("A1").Value & "|" & c.Value
Next c
f.Close
End Sub

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

how do I make the procedure work as a macro?

"JE McGimpsey" wrote:

You can modify the macro he

http://www.mcgimpsey.com/excel/mergedata.html

change

vTxtArr(i, 1) = vTxtArr(i, 1) & sDelim & vTxtArr(i, j)

to

vTxtArr(i, 1) = Left(vTxtArr(i, 1) & sDelim & vTxtArr(i, j), 1000)


In article ,
Bob wrote:

I have a column with about 6,000 rows.
I need a macro that will combine cells in the column with a pipe(|) between
each.
The key is that each row should be no more than 1,000 characters.

Check out David McRitchie's "Getting Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm


In article ,
Bob wrote:

how do I make the procedure work as a macro?

I got this to run,however, I get a "Object variable or with block variable
not set" error message at the line vTxtArr = rRng.Value

"JE McGimpsey" wrote:

Check out David McRitchie's "Getting Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm


In article ,
Bob wrote:

how do I make the procedure work as a macro?

I got this to run without error messages, however,I don't see where the
combined cells are (I did switch vTxtArr(i, 1) = vTxtArr(i, 1) & sDelim &
vTxtArr(i, j)as suggested) .

"JE McGimpsey" wrote:

Check out David McRitchie's "Getting Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm


In article ,
Bob wrote:

how do I make the procedure work as a macro?

The text from all columns is merged into the first column.

In article ,
Bob wrote:

I got this to run without error messages, however,I don't see where the
combined cells are (I did switch vTxtArr(i, 1) = vTxtArr(i, 1) & sDelim &
vTxtArr(i, j)as suggested) .