VBA Insert rows according to cell value AND number each new row

klight20

New Member
Joined
Jul 19, 2012
Messages
4
Hello, I have code that inserts a certain amount of rows according to a certain cells value:

Code:
Sub addrowsc()    
    Dim r, count As Range
    Dim LastRow As Long
    Dim temp As Integer
    Set r = Range("A:AM")
    Set count = Range("T22")
    LastRow = Range("T" & Rows.count).End(xlUp).Row
    For N = LastRow To 1 Step -1
        temp = Range("AI" & N)
        If (temp > 1) Then
            Rows(N + 1 & ":" & N + temp).Insert Shift:=xlDown
        End If
    Next N
End Sub
But I also want to number each row from 1 to value, so if I have the cell value set at 24, when the code is ran I want it to add 24 new rows, which it does, and I want it to number each row in column T according to the cell value, so from 1-24 for this example. Any help?
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
The easiest way would be to add a new variable that will keep track of the count:
Code:
Sub addrowsc()    
    Dim r, count As Range
    Dim LastRow As Long
    Dim temp As Integer
    [COLOR="#FF0000"]Dim i As Long[/COLOR]
    Set r = Range("A:AM")
    Set count = Range("T22")
    LastRow = Range("T" & Rows.count).End(xlUp).Row
    [COLOR="#FF0000"]i = 1[/COLOR]
    For N = LastRow To 1 Step -1
        temp = Range("AI" & N)
        If (temp > 1) Then
            Rows(N + 1 & ":" & N + temp).Insert Shift:=xlDown
            [COLOR="#FF0000"]Range("T" & N + 1).Value = i
            i = i + 1[/COLOR]
        End If
    Next N
End Sub
 

klight20

New Member
Joined
Jul 19, 2012
Messages
4
Thank you for the reply! I have had a delay in the project so have not been able to implement, but wanted to say thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,119
Messages
5,466,802
Members
406,498
Latest member
COACHTEEJ

This Week's Hot Topics

Top