Excel VBA For loop with variable index not working

Question

My data set looks like this

Name    ID

Alice   1

Alice   2

Alice   -4

Bob    4

Bob    7

2344   9

2344   10

4I    99

4I   04

What I want it to look like is this:

Name    ID

Alice   1

Alice   2

Alice   -4



Name    ID

Bob    4

Bob    7




Name    ID

2344   9

2344   10



Name    ID

4I    99

4I   04

Every time the name entry changes, I want to introduce 1 empty rows and a row of my header.

    Sub insertingrows()


  FinalColumn = Cells(1, Columns.Count).End(xlToLeft).Column
  FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

  For j = 3 To FinalRow

    If Cells(j, 2) <> Cells(j - 1, 2) Then
        'Rows(j & ":" & j + 2).Insert
        Rows(j).Insert
        Rows(1).Copy
        Rows(j + 1).Insert
        FinalRow = FinalRow + 2
        j = j + 2
    End If

  Next j



End Sub

My initial actual data has 3830 rows but even though I am updating my variable FinalRow inside the loop, the loop is still stopping around 3830 and is leaving a lot of the process unfinished. Is this a limitation of For? Should I use some other type of loop?

No, but I'll just add a sort command before hand in the VBA script. — Amatya
– Amatya, Commented Nov 22, 2013 at 19:44
I was about to post the answer but since @rdhs has already done it, I will pass on this one :) — Siddharth Rout
– Siddharth Rout, Commented Nov 22, 2013 at 19:54

Siddharth Rout · Accepted Answer · 2013-11-22 21:27:18Z

2

Let's say your worksheet looks like this

enter image description here

Put this code in a module

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Application.ScreenUpdating = False

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = lRow To 3 Step -1
            If .Cells(i, 1).Value <> .Cells(i - 1, 1).Value Then
                .Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                .Cells(i, 1).Value = "Name": .Cells(i, 2).Value = "ID"
                .Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            End If
        Next i
    End With

    Application.ScreenUpdating = True
End Sub

Output

enter image description here

answered Nov 22, 2013 at 21:27

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chel · Accepted Answer · 2013-11-22 19:47:02Z

1

The bounds of a For loop are calculated once, when the loop begins, and then become flat numbers. If you want to change the bounds, fall back to a Do loop:

j = 3
Do

    If Cells(j, 2) <> Cells(j - 1, 2) Then
        'Rows(j & ":" & j + 2).Insert
        Rows(j).Insert
        Rows(1).Copy
        Rows(j + 1).Insert
        FinalRow = FinalRow + 2
        j = j + 3
    End If

Loop While j < FinalRow

answered Nov 22, 2013 at 19:47

Chel

2,6231 gold badge20 silver badges25 bronze badges

4 Comments

Amatya Over a year ago

cool thanks! Great to know that Do accommodates change of bounds.

Amatya Over a year ago

As I wrote in the comments above, for the last 50 minutes, I have tried to run your script about 5 times and every time my excel has crashed, just frozen and not responding. But when I run my old script it runs fine (although it stops at row 3830). Any guesses?

Chel Over a year ago

When you increment j in the loop, are you doing it as j = j + 3 as I have, or j = j + 2 like in your code for the For loop?

Amatya Over a year ago

I did j=j+3 like you changed it since While does not have the increment of Next j.

Collectives™ on Stack Overflow

Excel VBA For loop with variable index not working

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related