insert variable number of rows
 

Hi
I would like to insert the number of rows of variable "myvalue"
i have wriiten this so far
For Each Cell In Range("MYCODES")
MYVALUE = Cell.Offset(0, 5).Value - 1
Cell.EntireRow.Rows(1, MYVALUE).Insert
Next Cell
I know insert line is incorrect but can't think what it should be
Thanks
Tina

insert variable number of rows
 

You need to loop in reverse (from highest to lowest) or you will screw up
your location.

Dim col as Long, firstRow as long, lastRow as long
Dim i as Long

col = Range("myCodes").Column
firstRow = Range("MyCodes").Rows(1).Row
lastRow = Range("Mycodes").Rows( _
Range("MyCodes").Rows.count).Row

for i = lastrow to firstrow step -1
myValue = cells(i,col).offset(0,5).Value - 1
cells(i,col).offset(1,0).Resize(myValue).EntireRow .Insert
Next

--
Regards,
Tom Ogilvy


"tina" wrote in message
...
Hi
I would like to insert the number of rows of variable "myvalue"
i have wriiten this so far
For Each Cell In Range("MYCODES")
MYVALUE = Cell.Offset(0, 5).Value - 1
Cell.EntireRow.Rows(1, MYVALUE).Insert
Next Cell
I know insert line is incorrect but can't think what it should be
Thanks
Tina