Increment final value in for statement

[nicoll]

Can anyone help me please with this macro.

If I add a row to a spreadsheet I want to increment the final value in a for
statement but it doesn't seem to work.

ie

For i = 4 To lastrow

(if statement which can add a row)
lastrow = lastrow + 1
Else: End If
next i


When I run this lastrow doesn't increment and last row stays at the original
value.

Thanks

Ian

 

[Don Lloyd]

Hi Ian,

When the For staement is executed, the current value value of lastrow is
retained as a counter for i for the whole of the For-Next loop.

Try using a counter in DO - LOOP UNTIL lastrow> counter.

Regards,
Don

 

[David McRitchie]

Hi Ian,
If you are running through a range and inserting or deleting
rows you want to start from the bottom. Otherwise, the lastcell is not
the only problem you would have -- you would also be skipping rows
or processing rows twice.

For i = lastrow to 4 step -1
....
next i

For more information on inserting and deleting rows and maintaining
your formulas see
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[nicoll]

Thanks Don's answer solved the problem.