CODEWITHSUNDEEP
excelvba
pinkpanther
pinkpanther

Reputation: 157

Move values from one column to another column

Is it possible using VBA to move values from ColumnC to ColumnB, ColumnD to ColumnC, and etc.?

After I import the text file from Excel and format it to text to column some of the data is not in the correct column.

For example,

ColA | ColB | ColC | ColD
1    | Sam1 | ABC  | 111
2    | Sam2 | DEF  | 222
3    | Sam3 | 101  | 333
4    | Sam4 | ABC  | 444

In this example, ColumnC with value of "ABC" and "DEF" is part of ColumnB. It is placed in ColumnC after I format it to text to column.
Also the value from ColumnD, should be in the ColumnC.

Upvotes: 1

Views: 1170

Answers (1)

GMalc
GMalc

Reputation: 2628

The code below will insert a blank cell in col3 when the value is a number, it will then combine the values from col2 and col3, and then delete col3. 

Dim ws As Worksheet, lRow As Long, i As Long

Set ws = ThisWorkbook.ActiveSheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

With ws
    For x = 1 To lRow
        If IsNumeric(.Cells(x, 3).Value) Then
            .Cells(x, 3).Insert Shift:=xlToRight
        End If

        .Cells(x, 2).Value = .Cells(x, 2).Value & " " & .Cells(x, 3).Value
    Next x

    .Columns(3).Delete
End With
End Sub

Upvotes: 1

Related Questions