CODEWITHSUNDEEP
vbaloopscopypaste
Lana
Lana

Reputation: 3

Copy odd numbered rows from one column into another column's even numbered row

I have a sheet that has about 860 records. The size can vary, but will not exceed a thousand rows.

I would like to apply the code to the entire sheet.

I want to copy the odd rows' values from column A and paste that value into Column B's even rows.

I want to copy the odd rows' values from column C and paste that value into Column D's even rows.

I want to copy the odd rows' values from column E and paste that value into Column F's even rows.

I do not know how to go to the next odd row (A5 to B4, C5 to D4, E5 to F4) until the values hit a blank, which will be the end of my sheet.

Sub myAwesomeMacro()

    For i = 1 To 1000
        Range("A3").Copy Range("B2")
        Range("A3").Copy Range("D2")
        Range("E3").Copy Range("F2")
    Next i
End Sub

What I am trying to achieve in this loop:
Copy A3 and Paste that value to B2
Copy C3 and Paste that value to D2
Copy E3 and Paste that value to F2

Then take the next odd row and go through the same pattern:
Copy A5 and Paste that value to B4
Copy C5 and Paste that value to D4
Copy E5 and Paste that value to F4

Then take the next odd row and go through the same pattern:
Copy A7 and Paste that value to B6
Copy C7 and Paste that value to D6
Copy E7 and Paste that value to F6

Upvotes: 0

Views: 697

Answers (2)

surendra choudhary
surendra choudhary

Reputation: 65

It will do your Job

Dim i As Long
Dim lastrow As Variant

With ActiveSheet
   lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
   For i = 3 To lastrow
      .Cells(i-1, "B") = .Cells(i, "A")
      .Cells(i-1, "D") = .Cells(i, "C")
      .Cells(i-1, "F") = .Cells(i, "E")
   Next i
End With

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166256

You can do something like this:

Sub myAwesomeMacro()
Dim i As Long

i = 3
Do While Len(Cells(i, 1).Value) > 0
    Cells(i, 1).Copy Cells(i-1, 2)
    Cells(i, 3).Copy Cells(i-1, 4)
    Cells(i, 5).Copy Cells(i-1, 6) 
    i = i + 1
Loop

End Sub

Upvotes: 0

Related Questions