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
 

Something like this should do the trick. It uses the value in sheet 2 A1 to
determine which row to start at and A2 to determine haow many rows to
insert...

Sub InsertRows()
With Sheets("Sheet2")
Sheets("Sheet1").Rows(.Range("A1")).Resize(.Range( "A2")).Insert
End With
End Sub
--
HTH...

Jim Thomlinson


"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?

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
 

Sorry, I forgot to mention that I did not understand you requirement "and so
on, for
100 times"???
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Something like this should do the trick. It uses the value in sheet 2 A1 to
determine which row to start at and A2 to determine haow many rows to
insert...

Sub InsertRows()
With Sheets("Sheet2")
Sheets("Sheet1").Rows(.Range("A1")).Resize(.Range( "A2")).Insert
End With
End Sub
--
HTH...

Jim Thomlinson


"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?

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