CODEWITHSUNDEEP
vbaexcel
Anthony Anzalone
Anthony Anzalone

Reputation: 61

How to insert rows based on values from specific cell?

I'm new to VBA and really trying to get working on a project for work, I apologize for my lack of knowledge but really trying to nail this for the boss.

What I'm trying to accomplish is as follows: I have a list of numbers that start from H2 and go to H200, each ranging from 1~150.

I am trying to write a macro that inserts rows under each number with the same count as the number. (ex. If the number is 42, create 42 rows under it. Then say the number under it is 13, then there would be 13 rows... and so on).

Current Code:

Sub InsertRow()

    i = 2
    count = Cells(i, H).Value

    Range("B2").EntireRow.Insert
    Range("B2").EntireRow.Resize(count).Insert shift:=xlDown

End Sub

Upvotes: 6

Views: 681

Answers (2)

Alex de Jong
Alex de Jong

Reputation: 1267

You are halfway there. You can use a for loop to go through all the cells. However, since you will insert rows and therefore expand the number of cells in the loop, I suggest to start from the bottom and go up:

For i = cells(Rows.count,"H").end(xlup).row to 2 step - 1
    count = Cells(i, 8).Value
    Range("B" & i + 1).EntireRow.Resize(count).Insert shift:=xlDown
Next i

Upvotes: 5

user4039065
user4039065

Reputation:

If you are inserting rows, you need to start at the bottom and work up.

Sub InsertRow()
    dim i as long

    with worksheets("sheet1")
        for i=.cells(.rows.count, "H").end(xlup).row-1 to 2 step -1
            .cells(i, "H").offset(1, 0).resize(.cells(i, "H").value2, 1).entirerow.insert
        next i
    end with
end sub

Upvotes: 4

Related Questions