Macro to combine cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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) .
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Combine columns from seperate sheets into new sheet- macro improve 4
Macro to Combine Data from Multiple Sheets with Cell References 4
Map characters to their relative positions in a cell 3
Convert code to lookup three criteria return fourth 15
Combine two cells without losing data cell formats 2
Advice on multiple macros, same code just a different column 9
Ubound for variant not working for some reason, can anyone see why? 1

Back
Top