Excel VBA loop through column and insert rows based on cell value

Question

Hello!

I am trying to build a code that loops through each cell in range C8:C3276 and inserts a new row below if the cell value is "Total".

Here is the code i have so far:

Sub Create_new_rows()

Dim rng As Range
Dim cell As Range
Set rng = Range("C8:C3276")

For Each cell In rng

If ActiveCell.Value = "Total" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.EntireRow.Insert
End If

Next cell

End Sub

Nothing happens when i execute the code. I assume the code is built incorrectly as the macro runs (i get no error message), but without doing anything.

Any help is greatly appreciated! :)

SJR · Accepted Answer · 2018-04-18 13:25:16Z

5

Two problems I think. 1) You should loop backwards as otherwise you will skip rows as you are adding more rows, and 2) in your loop you should have referred to cell rather than the ActiveCell which is never set.

Sub Create_new_rows()

Dim rng As Range, r As Long

Set rng = Range("C8:C3276")

For r = rng.Count To 1 Step -1
    If rng(r).Value = "Total" Then
        rng(r + 1).EntireRow.Insert
    End If
Next r

End Sub

answered Apr 18, 2018 at 13:25

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

3 Comments

DirtyDeffy Over a year ago

Thank you! This works perfectly. I would really love to understand exactly how your code works. Would you care to give a short explanation? :)

SJR Over a year ago

Sure. It uses a count variable rather than a range so that we can step backwards, starting at C3276 and ending at C8. rng(r) is just the rth cell in rng - e.g. when r=1 then rng(1) is C8. That said, using the Find method would be more efficient than looping through every cell.

DirtyDeffy Over a year ago

I figured that was the logic being applied - but better safe than sorry ;) Efficiency is not required in this particular case, as it was just a one time use. Thanks again for your help.

Collectives™ on Stack Overflow

Excel VBA loop through column and insert rows based on cell value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related