I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted cell.

Anybody able to help with this one?

Try
Sub insertrowsdynamic()
fr = Selection.Rows(1).Row
'MsgBox fr
lr = Selection.Rows.Count + fr
'MsgBox lr
nr = InputBox("How many rows")
'MsgBox nr
For i = lr To fr + 1 Step -1 'nr '(lr - fr + 1)
Rows(i).Resize(nr).Insert
'MsgBox i
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnHero" wrote in message
...
I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted cell.

Anybody able to help with this one?

Does this do what you want...

Sub InsertRows()
Dim R As Range, HowMany As Long
HowMany = InputBox("How many rows should be inserted?")
For Each R In Selection.Rows
R.Offset(1).Resize(HowMany).Insert
Next
End Sub

--
Rick (MVP - Excel)


"JohnHero" wrote in message
...
I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted cell.

Anybody able to help with this one?

I'd start from the bottom up <vbg.

Rick Rothstein wrote:

Does this do what you want...

Sub InsertRows()
Dim R As Range, HowMany As Long
HowMany = InputBox("How many rows should be inserted?")
For Each R In Selection.Rows
R.Offset(1).Resize(HowMany).Insert
Next
End Sub

--
Rick (MVP - Excel)

"JohnHero" wrote in message
...
I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted cell.

Anybody able to help with this one?

--

Dave Peterson

Actually, the code I posted works fine as is (I tested it<g).

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
I'd start from the bottom up <vbg.

Rick Rothstein wrote:

Does this do what you want...

Sub InsertRows()
Dim R As Range, HowMany As Long
HowMany = InputBox("How many rows should be inserted?")
For Each R In Selection.Rows
R.Offset(1).Resize(HowMany).Insert
Next
End Sub

--
Rick (MVP - Excel)

"JohnHero" wrote in message
...
I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted cell.

Anybody able to help with this one?

--

Dave Peterson

I put data in A1:H20.
I selected rows 1:20 and stepped through the macro using 3 at the howmany
prompt.

After the first loop, I had empty cells in rows 2:4.
After the second loop, I had empty cells in rows 2:7.
then 2:10, ...

I'd also be careful with the selection.

If I started with A1:B3 selected, then the first loop put empty cells in: A2:B4,
shifting the cells to the right.

After the second loop, I ended up with empty cells in: A2:b5 and C3:D4 and data
pushed further to the right.



Rick Rothstein wrote:

Actually, the code I posted works fine as is (I tested it<g).

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
I'd start from the bottom up <vbg.

Rick Rothstein wrote:

Does this do what you want...

Sub InsertRows()
Dim R As Range, HowMany As Long
HowMany = InputBox("How many rows should be inserted?")
For Each R In Selection.Rows
R.Offset(1).Resize(HowMany).Insert
Next
End Sub

--
Rick (MVP - Excel)

"JohnHero" wrote in message
...
I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted cell.

Anybody able to help with this one?

--

Dave Peterson

--

Dave Peterson

Well, I **just know** I tested the code before I posted it and I **know** it
worked AT THAT TIME... but now, I can't get the code to work correctly at
all. I have no idea what I did in my original tests to make it work, but
whatever I did then, I can't seem to repeat now. Okay, that's it... I'm off
trying to use the Selection for Inserts and Deletes from now on.<g Thanks
for watching my back on this.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
I put data in A1:H20.
I selected rows 1:20 and stepped through the macro using 3 at the howmany
prompt.

After the first loop, I had empty cells in rows 2:4.
After the second loop, I had empty cells in rows 2:7.
then 2:10, ...

I'd also be careful with the selection.

If I started with A1:B3 selected, then the first loop put empty cells in:
A2:B4,
shifting the cells to the right.

After the second loop, I ended up with empty cells in: A2:b5 and C3:D4
and data
pushed further to the right.



Rick Rothstein wrote:

Actually, the code I posted works fine as is (I tested it<g).

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
I'd start from the bottom up <vbg.

Rick Rothstein wrote:

Does this do what you want...

Sub InsertRows()
Dim R As Range, HowMany As Long
HowMany = InputBox("How many rows should be inserted?")
For Each R In Selection.Rows
R.Offset(1).Resize(HowMany).Insert
Next
End Sub

--
Rick (MVP - Excel)

"JohnHero" wrote in message
...
I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted
cell.

Anybody able to help with this one?

--

Dave Peterson

--

Dave Peterson

On Feb 25, 7:03*pm, "Don Guillett" wrote:
Try
Sub insertrowsdynamic()
fr = Selection.Rows(1).Row
'MsgBox fr
lr = Selection.Rows.Count + fr
'MsgBox lr
nr = InputBox("How many rows")
'MsgBox nr
For i = lr To fr + 1 Step -1 'nr '(lr - fr + 1)
Rows(i).Resize(nr).Insert
'MsgBox i
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"JohnHero" wrote in message

...



I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted cell.

Anybody able to help with this one?- Hide quoted text -

- Show quoted text -

THanks for the quick responses everyone.

You're welcome. <vbg

(and that never happens to me <I crack me up!.)

Rick Rothstein wrote:

Well, I **just know** I tested the code before I posted it and I **know** it
worked AT THAT TIME... but now, I can't get the code to work correctly at
all. I have no idea what I did in my original tests to make it work, but
whatever I did then, I can't seem to repeat now. Okay, that's it... I'm off
trying to use the Selection for Inserts and Deletes from now on.<g Thanks
for watching my back on this.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
I put data in A1:H20.
I selected rows 1:20 and stepped through the macro using 3 at the howmany
prompt.

After the first loop, I had empty cells in rows 2:4.
After the second loop, I had empty cells in rows 2:7.
then 2:10, ...

I'd also be careful with the selection.

If I started with A1:B3 selected, then the first loop put empty cells in:
A2:B4,
shifting the cells to the right.

After the second loop, I ended up with empty cells in: A2:b5 and C3:D4
and data
pushed further to the right.



Rick Rothstein wrote:

Actually, the code I posted works fine as is (I tested it<g).

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
I'd start from the bottom up <vbg.

Rick Rothstein wrote:

Does this do what you want...

Sub InsertRows()
Dim R As Range, HowMany As Long
HowMany = InputBox("How many rows should be inserted?")
For Each R In Selection.Rows
R.Offset(1).Resize(HowMany).Insert
Next
End Sub

--
Rick (MVP - Excel)

"JohnHero" wrote in message
...
I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted
cell.

Anybody able to help with this one?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Try


Sub AAA()
Dim N As Long
Dim L As Long
Dim R As Long
Dim M As Long
L = Selection.Rows.Count
R = Selection.Cells(1, 1).Row
N = Application.InputBox(prompt:="Number of rows to insert",
Type:=1)
If N <= 0 Then
Exit Sub
End If
For M = 1 To L
Cells(R + 1, 1).Resize(N).EntireRow.Insert
R = R + N + 1
Next M
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Thu, 25 Feb 2010 14:47:30 -0800 (PST), JohnHero
wrote:

I'd like to be able to have a macro that wouod work like this:

1) I highlight the rows

2) When I run the macro, it will ask me how many rows I'd like to
insert.

3) It would then insert x number of rows after each highlighted cell.

Anybody able to help with this one?