Simple (?) Macro

[NeedHelp]

I have three columns of data similar to:
A B C
a 1a melon
b 2b lettuce

I want column c to read a1amelon
that much is easy but I want this to apply to every row to the end of the data
so that it reads like:

a 1a a1amelon
b 2b b2blettuce
etc.

Help is appreciated

 

[Chip Pearson]

Try the following code:

Sub AAA()
Dim LastRow As Long
Dim FirstRow As Long
Dim RowNdx As Long
Dim WS As Worksheet
Dim S As String
Set WS = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED
FirstRow = 1 '<<< CHANGE AS REQUIRED
On Error GoTo ErrH
Application.EnableEvents = False
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = FirstRow To LastRow
S = .Cells(RowNdx, "C")
S = Mid(.Cells(RowNdx, "B"), 2, 1) & _
Left(.Cells(RowNdx, "B"), 1) & S
.Cells(RowNdx, "C").Value = S
Next RowNdx
End With
ErrH:
Application.EnableEvents = True
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Don Guillett]

Would this simple macro work to put all three in a NEW column to the right?

Sub combinecolumns()
lr = Cells(Rows.Count, "g").End(xlUp).Row
For Each c In Range("g2:g" & lr)
c.Offset(, 3) = c & c.Offset(, 1) & c.Offset(, 2)
Next
End Sub

OR as you requested to replace the third column
Sub combinecolumns1()
lr = Cells(Rows.Count, "g").End(xlUp).Row
For Each c In Range("g2:g" & lr)
c.Offset(, 2) = c & c.Offset(, 1) _
& c.Offset(, 2)
Next
End Sub

 

[ShaneDevenshire]

Hi,

You can use a non-macro solution:

1. In column D enter the formula =A1&B1&C1
2. Copy this formula down as far a necessary
3. Select all the formulas and choose Copy.
4. Click on C1 and choose Edit, Paste Special, Values.