insert rows

  • Thread starter Thread starter project manager
  • Start date Start date
P

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...
 
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
 
thanks it almost works but it is inserting the row above the number not
below...
 
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
 
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
 
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
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top