Adding rows dynamically via macro
 

Hi,

I understand that if I add 4 rows from row 17 onwards, the command
reads as follows:

Sub Insert()
Rows("17:20").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
End Sub

I would like to modify this for 3 scenarios:

Scenario 1: Now, what if I don't specify 4 rows, but I want it to read
the number-of-rows-to-be-added from a certain cell from a different
worksheet eg: T4 or cell(4, 20) from worksheet 2, because that number
is to modifiable?

Scenario 2: What if I want to add 4 rows, but not necessarily from row
17 - instead, I might want to add 4 rows from any initial row?

Scenario 3: Now combine the 2 scenarios. What if for row 4, I want it
to add a number of rows specified in cell(4, 20) and then for row 5, I
want to add a number of rows specified in cell (5, 20) and so on, for
100 times?

Thanking you in advance.

SD

Adding rows dynamically via macro
 

See responses to your scenarios below. Hope this helps.

"sleepingdragon2k2" wrote:

Hi,

I understand that if I add 4 rows from row 17 onwards, the command
reads as follows:

Sub Insert()
Rows("17:20").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
End Sub

I would like to modify this for 3 scenarios:

Scenario 1: Now, what if I don't specify 4 rows, but I want it to read
the number-of-rows-to-be-added from a certain cell from a different
worksheet eg: T4 or cell(4, 20) from worksheet 2, because that number
is to modifiable?

Sub Insert()
Dim StartRow as Long
Dim RowsToAdd as Long
StartRow=17
RowsToAdd=Range("T4").Value - 1
Rows(StartRow & ":" & StartRow + RowsToAdd ).Insert
End Sub

Scenario 2: What if I want to add 4 rows, but not necessarily from row
17 - instead, I might want to add 4 rows from any initial row?

Use the same code from Scenario 1, but change the value of StartRow

Scenario 3: Now combine the 2 scenarios. What if for row 4, I want it
to add a number of rows specified in cell(4, 20) and then for row 5, I
want to add a number of rows specified in cell (5, 20) and so on, for
100 times?

Sub Insert()
Dim StartRow as Long
Dim RowsToAdd as Long
Dim Counter as Long
StartRow=4
For Counter=0 to 99
RowsToAdd=Cells(StartRow+Counter,20).Value - 1
Rows(StartRow + Counter & ":" & StartRow + Counter + RowsToAdd ).Insert
End Sub


Thanking you in advance.

SD