Inserting #of rows based on dcount

Lisa

Board Regular
Joined
Apr 28, 2002
Messages
80
Please forgive another newbie question.

I'm trying to insert a specific number of rows based on a dcount return. Based on recording, the code for insertion is

Selection.EntireRow.Insert

Using F2 for insert shows me this:

Insert([shift])

I don't understand what this is telling me. Is there a way to put a variable in to tell the routine how many rows to insert? Such as the range name where the dcount is? I've attempted several things, but think I'm misunderstanding the required syntax.

Thanks for any help.
Lisa
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi Lisa,

Since Insert inserts one row, a loop is needed. The following code will insert 10 new rows before row 5:

Dim i As Integer
'insert 10 rows before row 5
For i = 1 To 10
Rows(5).Insert
Next i
 
Upvote 0
I'll add to Damon's xl'nt code with the example of taking a specific range's value:

Code:
Sub insrt()
Dim i As Long, n As Long
i = Sheet1.[a1].Value 'change cell A1 and Sheet # to that of your choice
For n = 1 To i
Rows(5).Insert 'Change '5' to row # (target) of your choice
Next n

End Sub
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by nateo on 2002-04-30 10:26
 
Upvote 0
Thanks Damon and Nate for your great responses.

I think this will work, but there's another quirk. The place where I want the row(s) inserted is a named range. I had to do this since I will be adding various numbers of rows to each section. So instead of Row(5), I need some way to reference the range named "insert91". BTW, the dcount range where I'm getting the number of rows and the range where I need to insert are on different sheets of the same book. I was playing with the code and (obviously) am stuck on how to get a row reference before the .insert.

Sub addb()
Dim i As Long
i = Sheets("information").Range("bcount").Value
For i = 1 To i
'Rows(5).Insert 'Change '5' to row # of your choice
Sheets("Detail").Range("insert91").Insert
Next i

End Sub

Again, thanks for the help. I hope not to become a pest!

Lisa
 
Upvote 0
Your code looks good to me, I added another variable as one variable doing two different things looks dangerous, like:

Code:
Sub addb()
Dim i As Long, n As Long
i = Sheets("information").Range("bcount").Value
For n = 1 To i
Sheets("Detail").Range("insert91").Insert
Next n

End Sub

I defined insert91 as the entire row. Does this not work?

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 10:43
 
Upvote 0
To perform the insert 1 row above the defined range 'insert91' try:

Code:
Sub addb()
Dim i As Long, n As Long
i = Sheets("information").Range("bcount").Value
For n = 1 To i
Sheets("Detail").Range("insert91").Offset(-1).Insert 'one before
Next n

End Sub

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 10:42
 
Upvote 0
I think I've botched the question. Do you mean 'insert91' is a single cell and instead of inserting single cells, you want a row. Try:

Code:
Sub addb600()
Dim i As Long, n As Long
i = Sheets("information").Range("bcount").Value
For n = 1 To i
Sheets("Detail").Range("insert91").EntireRow.Insert
Next n

End Sub

Getting warmer?

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 10:49
 
Upvote 0
Thanks, Nate

It's inserting, but only in column A. That's where my "insert91" range is defined. So it's pushing all the column A data down the proper number of rows instead of inserting new rows. Do I need to put an "Entire.Row" before the .insert?

Again, thanks for your help.

Lisa
 
Upvote 0
Think we were posting at the same time, see my response above your last one. The answer is yes (w/o the .)

Incidentally, I'm in the midwest too, Minneapolis. And it's not snowing! :)
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-30 10:55
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top