Insert rows based on cell value (simple VBA, I assume...?)

newbie28 Posts 1 Registration date Wednesday August 21, 2013 Status Member Last seen August 21, 2013 - Aug 21, 2013 at 04:15 AM
 mm - Nov 13, 2014 at 06:49 AM

sorry about my newbie question, I think it's very simple to solve, but have spent a few hours on google now and haven't found the right answer yet... :-(

What I'm looking for is to have "x" amount of rows added according to the value (number) chosen in cell F22 on my sheet.

Meaning: if the value of cell F22 = 4, then 4 rows will be added below row 23.
If possible to include formats, then I would like the cells B,C,D,E,F in the corresponding rows to be framed.
Is this possible?

Please don't hesitate to ask any questions if you need more info from me in order to answer.

I would truly appreciate any help I can get on this!

Thank you!


P.S.: I use Excel 2007

1 response

Blocked Profile
Aug 21, 2013 at 01:20 PM
Good afternoon, My morning has been busy or I would have responded earlier. I really hope this isnt a school project.

Sub insert()

Dim x
Dim rws
Dim magicstart
Dim startrange
magicstart = 22
startrange = "F" & CStr(magicstart)
rws = Selection.Value
Rows(CStr(magicstart + 1)).Select
For x = 0 To rws - 1
Selection.insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B" & CStr(magicstart + 1 + x) & ":" & "E" & CStr(magicstart + 1 + x)).Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End Sub

Please take a look and you may very well have to make changes for it to work EXACTLY how you wish. The point is, it is a good start.
but how to add next search function in above code