Inserting new lines with corresponding number

  • Thread starter Thread starter tripflex
  • Start date Start date
T

tripflex

Alright here's the deal. I have a sheet that has a list of items,
quantities, unit price and extended price. Right now they are numbered 1-20
but they are on row 11 - 30.

You can see a screen shot here to see what i mean

http://img134.imageshack.us/img134/2361/excelh.jpg

Well anyways, i want to see if anybody knows how i would be able to insert a
new line and put the corresponding number in there.

So lets say for instance i need 50 items instead of only 20. I wanted to
see if anybody knows how i would be able to write a macro where it will
automatically add new lines below 20, and go all the way through 50.

So after running the macro it will go from only being 20 to being 50 or
however many items are needed.

This will help me out a very lot and save me a lot of time if someone might
be able to assist me with this.

Thank you for your time and help.


EDIT:

I had a reply with this code
This little macro will do the job...
Sub test()
Cells(31, 1).Select
For i = 50 To 21 Step -1
ActiveCell.EntireRow.Insert
ActiveCell.Value = i
Next
End Sub

But i cant seem to figure out how to make it duplicate the text that is
already in the cells. So say for instance we are entering 5 more lines but I
need those 5 more lines to be automatically numbered 21-25 so say after the
macro is ran the image above will go from being 1-20 to 1-25.

Thanks guys!!
 
Use this
Sub test()
noOfRows = InputBox("How many rows do you want to add?")
noOfRows = noOfRows + 30
Cells(31, 1).Select
For i = 31 To noOfRows
ActiveCell.EntireRow.Insert
ActiveCell.Value = i
ActiveCell.Offset(-1, 0).EntireRow.Copy Destination:=ActiveCell
Next
End Sub

You will be prompted for how many rows you want to add... new rows will be
added from Row32 and copy the values from Row31..
 
Back
Top