Tom:

Thanks, it work, I do not care anyone said, you are
the greatest.

Lillian
,,,Original Message-----
Dim cell as Range, sStr as String
Dim i as long
for each cell in Range(cells(1,"J"),cells
(rows.count,"J"))
sStr = cell
for i = 1 to 7
sStr = sStr & cell.Offset(0,i)
Next
cell.Value = sStr
Next
Columns("K:Q").Delete

--
Regards,
Tom Ogilvy

"Lillian" wrote in
message
...
Rick:

The last Range(Cells(1, "K"), Cells
(Rows.Count, "Q")).ClearContents, is only clear
contents,
but I need to delete from Q to K column, how we do
that.
thanks.

Lillian

-----Original Message-----
Hi,

Tom Ogilvy is great! He's offered great support over
the
years for many people.

To delete the data in the columns K to Q, see if this
added line of code to clear the contents (at the
bottom)
does what you want. I also added a space between the
concatenated values if you would like. You can adjust
it
to suit your needs, with a dash or comma between the
values (instead of a blank space) - or just leave it
the
way it was. You can also change the Rows.Count to
some
smaller value (that is, to whatever is your last row
of
data) so that it will run faster.

Sub ConcatenateRev1()

Dim cell As Range, sStr As String
Dim i As Long

For Each cell In Range(Cells(1, "J"), Cells
(Rows.Count, "J"))
sStr = cell
For i = 1 To 7
sStr = sStr & " " & cell.Offset(0, i)
Next i
cell.Value = sStr
Next cell

Range(Cells(1, "K"), Cells
(Rows.Count, "Q")).ClearContents

End Sub

I hope that helps.

-----Original Message-----
Tom:
I use this macro, it moved all the data from
ColumnJ
toCOlumnQ to ColumnJ, it good, but I need to delete
out
ColumnK to ColumnQ, how we do that?

One more things your great.

Lillain
-----Original Message-----
Dim cell as Range, sStr as String
Dim i as long
for each cell in Range(cells(1,"J"),cells
(rows.count,"J"))
sStr = cell
for i = 1 to 7
sStr = sStr & cell.Offset(0,i)
Next
cell.Value = sStr
Next

--
Regards,
Tom Ogilvy


"Lillian"
wrote
in
message
.. .
I have excel spreedsheet has a lots of columns , I
need
to concanteante from J column to Q column, it mean
concanteante those column data to J column using
macro
VBA.

thanks.


Lillian


.

.

.



.