CODEWITHSUNDEEP
excelvba
Billy
Billy

Reputation: 5

How to make an Excel macro loop to cut 2nd row and paste to the 1st row

I am very new to Excel VBA and I have a report with about 200 entries, however each entry is split on two rows so that the first 8 columns on row 1 is the first half of the data and the first 8 columns on row 2 is the second half. I want to take the 8 columns on row 2, cut and paste them at the end of the 1st row, delete the empty row, and continue through the rest of the report.

I used Record Macro to get code that cuts range "A2:H2" and pastes it to "I1" and then I repeated that for the next entry. I know that isn't efficient because it isn't in a loop and it is very long. Any help would be greatly appreciated. Here is my code sample:

    Range("A2:H2").Select
    Selection.Cut
    Range("I1").Select
    ActiveSheet.Paste

    Range("A4:H4").Select
    Selection.Cut
    Range("I3").Select
    ActiveSheet.Paste

    Range("A6:H6").Select
    Selection.Cut
    Range("I5").Select
    ActiveSheet.Paste

    Range("A8:H8").Select
    Selection.Cut
    Range("I7").Select
    ActiveSheet.Paste

    Range("2:2,4:4,6:6,8:8").Select
    Selection.Delete Shift:=xlUp

Result would be data from A1:P1 and only 200 rows of data instead of 400. Thanks!

Upvotes: 0

Views: 225

Answers (1)

CLR
CLR

Reputation: 12254

This is a loop that replaces your code:

Dim r As Long, toprow As Long, bottomrow As Long
toprow = 2
bottomrow = 8
For r = toprow To bottomrow Step 2
    Range("A" & r & ":H" & r).Cut Range("I" & r - 1)
Next

If you want the spacing removed, the loop inner should be

Range("A" & r & ":H" & r).Cut Range("I" & r / 2)

Upvotes: 1

Related Questions