VBA Excel - insert copied rows in For loop

Question

I am doing a For Each loop through a set of rows. And I am copying those rows a different set of times. For instance, row 2 will be copied 3 times but row 3 is not gonna be copied and row 4 is gonna be copied 2 times.

This number of copied rows is set by a field in the excel - totalDS

The problem is, when I insert this code

Set RowRange = sh.Range("A2:A" & LastRow)

For Each rowiter In RowRange

    Dim i As Integer

    For i = 2 To totalDS

        With Worksheets("Sheet1")
            .Rows(rowiter.Row).Copy
            .Rows(rowiter.Row).Offset(1).Insert Shift:=xlDown
            Application.CutCopyMode = False
        End With

    Next i

Next rowiter

I will have a row right next to the one I copied and that is correct. But i need that my next step of the For loop goes to the next uncopied row. As it is right now, the For loop will just go for the next line so it will keep copying the same line over and over.

Any tips on this?

As I can tell so far, this won't work with a for/each loop. What is the aim of your approach? Do you want every line in the Range being duplicated x times? — Pᴇʜ
– Pᴇʜ, Commented Mar 17, 2017 at 12:41

Pᴇʜ · Accepted Answer · 2017-03-17 12:58:23Z

I just did this example test sub to show how it could work. This sub goes from row i=2 to row LastRow=10 and duplicates every row totalDS=2 times.

Sub test()
    Dim sh As Worksheet
    Set sh = Worksheets("Sheet1")

    Dim LastRow As Long
    LastRow = 10

    Dim totalDS As Long
    totalDS = 2


    Dim i As Long, j As Long

    i = 2 'we start at row i
    Do While i <= LastRow 'and we end at LastRow

        For j = 1 To totalDS 'insert row totalDs times
            sh.Rows(i).Copy 'copy row
            sh.Rows(i).Offset(1).Insert Shift:=xlDown
        Next j

        i = i + totalDS + 1 
           'We need to go totalDs rows forward (because we inserted
           'totalDS rows) + 1 row further to get the next new row.

        LastRow = LastRow + totalDS
           'We also need to increase LastRow by totalDS because we
           'inserted totalDS rows and LastRow also moved totalDS rows down.
    Loop

    Application.CutCopyMode = False 'it's enough to use this at the very last end
End Sub

steveo40 · Accepted Answer · 2017-03-17 12:29:44Z

0

When you insert a row, try incrementing your index by 1. For example i = i + 1

answered Mar 17, 2017 at 12:29

steveo40

9315 silver badges11 bronze badges

Collectives™ on Stack Overflow

VBA Excel - insert copied rows in For loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related