Macro to combine text

[Louise1]

Hi all

I'm stuck on this problem and hoping someone can help.

I have a list of data- (up to 1000 rows)
I want to combine the data in rows C and D into column C (with 3 spaces between) then delete column D

i.e.
Before
Column C100 = 0.4
Column D100 = 1A

After
Column C100 = 0.4 1A
Column D100 =

If anyone can help that would be amazing, Thanks!

 

[alansidman]

Try this:

Option Explicit


Sub combine()
    Application.ScreenUpdating = False
    Dim c As Range, rng As Range
    Dim lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Set rng = Range("C1:C" & lr)
    For Each c In rng
        c = c & "   " & c.Offset(0, 1)
    Next c
    Range("D1").EntireColumn.ClearContents
    Application.ScreenUpdating = True
    MsgBox "Complete"
End Sub

 

[Norie]

Not totally clear where the list starts so I've assumed it's row 2.

Dim rng As Range
    Set rng = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    With rng
        .Value = Evaluate("INDEX(" & .Address & "&""   ""&" & .Offset(, 1).Address & ",,1)")
        .Offset(, 1).Delete
    End With

 

[Louise1]

They both work perfectly.
Thank you! that's great.