2

I have recorded this macro in excel. The idea is to go through all rows (100) and repeat the SUB. I think i figured the proper algorithm (I have basic Turbo Pascal knowlege).

Here is my algorithm:

Create a loop that is using a variable, i, for counting. For i from 2 (we start from row 2, row 1 has headers) to 200 (even if we initially have 100 rows by the time the scrip will finish executing we will have doubled the amount of rows)

Do the sub

How do I make Rows("2:2") for example to reference the current value of i.

How do Rows("3:3") to use the value i+1.

How do I make Range("B2") to use the value B(i)

I am stuck with the loop and the proper syntax Please help. Also since I want to get deeper into the topic could you please suggest some good sources? I was not really interested in coding after during high school, but now I want to get deeper into the topic.

Sub Macro2()
    Rows("2:2").Select
    Selection.Copy
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("A3").Select
    ActiveSheet.Paste
End Sub
Victor Warhol
  • 23
  • 2

1 Answers1

2

Something like this:

Sub Macro2()
Dim i As Long
For i = 100 To 2 Step -1
    Rows(i).Copy
    Rows(i + 1).Insert shift:=xlDown
    Cells(i, 2).Cut Cells(i + 1, 1)
Next i
End Sub

I removed the .Select and .Activate It slows down the code. See HERE for good guidance on such.

When adding or subtracting rows it is best to iterate from bottom to top. The Step - 1 does that. It starts at row 100 and goes up till row 2.

Another note you will want to designate the sheet so that it looks at the correct sheet:

Sub Macro2()
Dim ws as Worksheet
Dim i As Long

Set ws = Sheets("Sheet1") 'Change to your sheet.
For i = 100 To 2 Step -1
    ws.Rows(i).Copy
    ws.Rows(i + 1).Insert shift:=xlDown
    ws.Cells(i, 2).Cut ws.Cells(i + 1, 1)
Next i
End Sub
Community
  • 1
  • 1
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • That worked like warm knife through butter. Thank you. Did I understand corectly that we are processing the rows from the bottom up? Any advice on where to learn more? – Victor Warhol Apr 13 '16 at 19:21
  • @VictorWarhol Glad it worked. Please mark as correct by clicking the grey/green check by the answer. To learn, there are many free websites that will walk through step by step. Start [HERE](http://www.techonthenet.com/excel/macros/what_is_vba2013.php). – Scott Craner Apr 13 '16 at 19:25
  • Great! Thank you again. – Victor Warhol Apr 13 '16 at 19:29