Inserting #of rows based on dcount

[Lisa]

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

[Damon Ostrander]

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

[NateO]

I'll add to Damon's xl'nt code with the example of taking a specific range's value:

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, NateO

[ This Message was edited by: nateo on 2002-04-30 10:26 ]

[Lisa]

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

[NateO]

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

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, NateO

[ This Message was edited by: NateO on 2002-04-30 10:43 ]

[NateO]

To perform the insert 1 row above the defined range 'insert91' try:

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, NateO

[ This Message was edited by: NateO on 2002-04-30 10:42 ]

[NateO]

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:

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, NateO

[ This Message was edited by: NateO on 2002-04-30 10:49 ]

[Lisa]

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

[NateO]

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, NateO

[ This Message was edited by: NateO on 2002-04-30 10:55 ]

[Lisa]

Nate!

Thanks so much! It works beautifully. Now to the next module....

Lisa