hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

Assumes A1 contains the value for the number of rows.
Range("D7") is arbitrary. You can start with ActiveCell or any other
cell designation.

Sub dk()
Range("D7").Resize(Range("A1").Value, 1).EntireRow.Insert
End Sub




"project manager" wrote in
message ...
hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

thanks it almost works but it is inserting the row above the number not
below...

"Mike H" wrote:

On reflection I prefer this

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
Rows(i).Resize(Cells(i, mc).Value).Insert
End If
Next i
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub InsertRows()
Dim i As Long, x As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
For x = 1 To Cells(i, mc).Value
Rows(i).Insert
Next x
End If
Next i
End Sub


Mike

"project manager" wrote:

hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

Hi,

That's just a slight change

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i - 1, mc)) Then
Rows(i).Resize(Cells(i - 1, mc).Value).Insert
End If
Next i
End Sub

Mike

"project manager" wrote:

thanks it almost works but it is inserting the row above the number not
below...

"Mike H" wrote:

On reflection I prefer this

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
Rows(i).Resize(Cells(i, mc).Value).Insert
End If
Next i
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub InsertRows()
Dim i As Long, x As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
For x = 1 To Cells(i, mc).Value
Rows(i).Insert
Next x
End If
Next i
End Sub


Mike

"project manager" wrote:

hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

cheers, that just come back with a red circle error 400

"Mike H" wrote:

Hi,

That's just a slight change

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i - 1, mc)) Then
Rows(i).Resize(Cells(i - 1, mc).Value).Insert
End If
Next i
End Sub

Mike

"project manager" wrote:

thanks it almost works but it is inserting the row above the number not
below...

"Mike H" wrote:

On reflection I prefer this

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
Rows(i).Resize(Cells(i, mc).Value).Insert
End If
Next i
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub InsertRows()
Dim i As Long, x As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
For x = 1 To Cells(i, mc).Value
Rows(i).Insert
Next x
End If
Next i
End Sub


Mike

"project manager" wrote:

hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

Hi,

The only difference between the 2 pieces of code are the addition of -1 on 2
lines and all they do in effect is make the code ignore the last line because
there's already lots of empty rows below that.

Did you copy it exactly as posted? otherwise I can't make the code generate
an error even if column M is empty, all text or a mixture of text and numbers.

Mike

"project manager" wrote:

cheers, that just come back with a red circle error 400

"Mike H" wrote:

Hi,

That's just a slight change

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i - 1, mc)) Then
Rows(i).Resize(Cells(i - 1, mc).Value).Insert
End If
Next i
End Sub

Mike

"project manager" wrote:

thanks it almost works but it is inserting the row above the number not
below...

"Mike H" wrote:

On reflection I prefer this

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
Rows(i).Resize(Cells(i, mc).Value).Insert
End If
Next i
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub InsertRows()
Dim i As Long, x As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
For x = 1 To Cells(i, mc).Value
Rows(i).Insert
Next x
End If
Next i
End Sub


Mike

"project manager" wrote:

hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

yes i copied into a blank sheet it runs it and then generates an error -400

"Mike H" wrote:

Hi,

The only difference between the 2 pieces of code are the addition of -1 on 2
lines and all they do in effect is make the code ignore the last line because
there's already lots of empty rows below that.

Did you copy it exactly as posted? otherwise I can't make the code generate
an error even if column M is empty, all text or a mixture of text and numbers.

Mike

"project manager" wrote:

cheers, that just come back with a red circle error 400

"Mike H" wrote:

Hi,

That's just a slight change

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i - 1, mc)) Then
Rows(i).Resize(Cells(i - 1, mc).Value).Insert
End If
Next i
End Sub

Mike

"project manager" wrote:

thanks it almost works but it is inserting the row above the number not
below...

"Mike H" wrote:

On reflection I prefer this

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
Rows(i).Resize(Cells(i, mc).Value).Insert
End If
Next i
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub InsertRows()
Dim i As Long, x As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
For x = 1 To Cells(i, mc).Value
Rows(i).Insert
Next x
End If
Next i
End Sub


Mike

"project manager" wrote:

hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

its excel 2007 if that helps

"project manager" wrote:

yes i copied into a blank sheet it runs it and then generates an error -400

"Mike H" wrote:

Hi,

The only difference between the 2 pieces of code are the addition of -1 on 2
lines and all they do in effect is make the code ignore the last line because
there's already lots of empty rows below that.

Did you copy it exactly as posted? otherwise I can't make the code generate
an error even if column M is empty, all text or a mixture of text and numbers.

Mike

"project manager" wrote:

cheers, that just come back with a red circle error 400

"Mike H" wrote:

Hi,

That's just a slight change

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i - 1, mc)) Then
Rows(i).Resize(Cells(i - 1, mc).Value).Insert
End If
Next i
End Sub

Mike

"project manager" wrote:

thanks it almost works but it is inserting the row above the number not
below...

"Mike H" wrote:

On reflection I prefer this

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
Rows(i).Resize(Cells(i, mc).Value).Insert
End If
Next i
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub InsertRows()
Dim i As Long, x As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
For x = 1 To Cells(i, mc).Value
Rows(i).Insert
Next x
End If
Next i
End Sub


Mike

"project manager" wrote:

hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

I have no knowledge of E2007 but I doubt they have changed VBA, Did you get
the same error on the first bit of code, if not then I'm baffled

Mike

"project manager" wrote:

its excel 2007 if that helps

"project manager" wrote:

yes i copied into a blank sheet it runs it and then generates an error -400

"Mike H" wrote:

Hi,

The only difference between the 2 pieces of code are the addition of -1 on 2
lines and all they do in effect is make the code ignore the last line because
there's already lots of empty rows below that.

Did you copy it exactly as posted? otherwise I can't make the code generate
an error even if column M is empty, all text or a mixture of text and numbers.

Mike

"project manager" wrote:

cheers, that just come back with a red circle error 400

"Mike H" wrote:

Hi,

That's just a slight change

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i - 1, mc)) Then
Rows(i).Resize(Cells(i - 1, mc).Value).Insert
End If
Next i
End Sub

Mike

"project manager" wrote:

thanks it almost works but it is inserting the row above the number not
below...

"Mike H" wrote:

On reflection I prefer this

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
Rows(i).Resize(Cells(i, mc).Value).Insert
End If
Next i
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub InsertRows()
Dim i As Long, x As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
For x = 1 To Cells(i, mc).Value
Rows(i).Insert
Next x
End If
Next i
End Sub


Mike

"project manager" wrote:

hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...

Found this if it helps, i've tried the old code and it gets the error too.

Excel Error 400 is a "catch-all" error for something that Excel cannot
resolve in de-coding Macro lines. It is applied to Application Define Errors
or Object Define Errors.

Like you have a Variable defined as a type of number, but the data you are
loading into it is a string or object. Bang-Error: 400.

"Mike H" wrote:

I have no knowledge of E2007 but I doubt they have changed VBA, Did you get
the same error on the first bit of code, if not then I'm baffled

Mike

"project manager" wrote:

its excel 2007 if that helps

"project manager" wrote:

yes i copied into a blank sheet it runs it and then generates an error -400

"Mike H" wrote:

Hi,

The only difference between the 2 pieces of code are the addition of -1 on 2
lines and all they do in effect is make the code ignore the last line because
there's already lots of empty rows below that.

Did you copy it exactly as posted? otherwise I can't make the code generate
an error even if column M is empty, all text or a mixture of text and numbers.

Mike

"project manager" wrote:

cheers, that just come back with a red circle error 400

"Mike H" wrote:

Hi,

That's just a slight change

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i - 1, mc)) Then
Rows(i).Resize(Cells(i - 1, mc).Value).Insert
End If
Next i
End Sub

Mike

"project manager" wrote:

thanks it almost works but it is inserting the row above the number not
below...

"Mike H" wrote:

On reflection I prefer this

Sub InsertRows()
Dim i As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
Rows(i).Resize(Cells(i, mc).Value).Insert
End If
Next i
End Sub

Mike

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub InsertRows()
Dim i As Long, x As Long
mc = "M"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsNumeric(Cells(i, mc)) Then
For x = 1 To Cells(i, mc).Value
Rows(i).Insert
Next x
End If
Next i
End Sub


Mike

"project manager" wrote:

hi,

I'm trying to write a macro which inserts rows depending on a number in a
cell at the end of the row/information.

Ie. if the number in the cell is 4 insert 4 rows. number = 7, 7 rows.

If needs to sweep through column m looking for numbers and inserting rows
until the it goes blank.

cheers for any advise in advance...