insert rows

[project manager]

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...

 

[JLGWhiz]

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]

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

 

[Mike H]

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]

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

 

[Mike H]

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]

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

 

[project manager]

its excel 2007 if that helps

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

 

[Mike H]

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]

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.