Concatenate from J to Q column using VBA

  • Thread starter Thread starter Lillian
  • Start date Start date
L

Lillian

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
 
please explain in more details as to what sort of data do you need t
move and if there are entries on the same row in more the one colum
haw do you want the data handle
 
I need to move all the data from ColumnJ to columnQ to
ColumnJ, it meant that concatenate to columnJ.

Lillian
 
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
 
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
 
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)
cell.Offset(0,i).ClearContents
Next
cell.Value = sStr
Next
 
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.
 
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
Click to expand...
(Rows.Count, "Q")).ClearContents
 
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
 
Tom:

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

Lillian
,,,Original Message-----
 
Back
Top