Excel VBA Inserting a Row in a Loop for Each Occurrence

Question

I'm trying to build a VBA application that checks for a certain value, then adds a row on top for each time this value is found.

    Sub copy()

    Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("B2:B10")

For Each row In rng.Rows
  For Each cell In row.Cells
    If cell.value = "test" Then
        MsgBox "found" + cell.Address
        cell.EntireRow.Insert
    End If
    
  Next cell
Next row


End Sub

Every time I try to run this function, however, it keeps adding rows on top of each other continuously and not for each occurrence.

What do you mean by "adding rows on top of each other"? Maybe it would help if you show us the input data plus the output as you expect it. — Ike
– Ike, Commented Oct 25, 2021 at 9:36
Excel VBA has nothing to do with VB.NET, so please remove the tag to avoid confusion. — Hel O'Ween
– Hel O'Ween, Commented Oct 25, 2021 at 9:39
Yes of course, apologies, it keeps appending rows on top of each other continuously leaving a large white space, instead I want it to only append rows for where "test" occurs in a cell. I'll leave a screenshot — KylianMbappe
– KylianMbappe, Commented Oct 25, 2021 at 9:39

Raymond Wu · Accepted Answer · 2021-10-25 09:50:46Z

2

If you loop the cells from top to bottom, adding the row will push your original range down, causing the next loop to evaluate the previous cell.

To avoid this, loop backwards (i.e. bottom to top):

Sub copy_test()

    Dim rng As Range
    Set rng = Range("B2:B10")
        
    Dim i As Long
        
    For i = rng.Cells.Count To 1 Step -1
        If rng.Cells(i).Value = "test" Then
            Debug.Print "Found"
            rng.Cells(i).EntireRow.Insert
        End If
    Next i
End Sub

Note: Set rng = Range("B2:B10") is telling VBA that you are referring to Cells B2:B10 of the ActiveSheet which might not be what you want.

Please fully qualify your range to avoid this. (e.g. ThisWorkBook.Worksheets("Sheet1").Range("B2:B10") or use the code name of the worksheet Sheet1.Range("B2:B10").)

answered Oct 25, 2021 at 9:50

Raymond Wu

3,4272 gold badges9 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA Inserting a Row in a Loop for Each Occurrence

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related