Reszing a loop - Possible?

[MattShoreson]

Code is as follows:

lngLoopLength = .UsedRange.Rows.Count

For lngIndex = 3 To lngLoopLength

"if condition met then
InsertRow
end if

lngLoopLength = .UsedRange.Rows.Count

Next lngIndex

the loop if not resizing using this method.
Ideas anyone?
tia,
Matt.

[Andrew Taylor]

It isn't mentioned in the help, but it appears that the
From, To and Step parameters of a For statement
are only evaluated once. You could do something like:

lngIndex = 3
Do While lngIndex <= .UsedRange.Rows.Count
' do stuff
lngIndex = lngIndex +1
Loop

(which works because the While condition is fully
evaluated every time round the loop)

Andrew


MattShoreson wrote:
> Code is as follows:
>
> lngLoopLength = .UsedRange.Rows.Count
>
> For lngIndex = 3 To lngLoopLength
>
> "if condition met then
> InsertRow
> end if
>
> lngLoopLength = .UsedRange.Rows.Count
>
> Next lngIndex
>
> the loop if not resizing using this method.
> Ideas anyone?
> tia,
> Matt.
>
>

[MattShoreson]

sorry forgot to post...
you can also do

for i = 1 to .usedrange.row.count step -1
do stuff
next i

essentially working backwards.

[NickHK]

Matt,
Does this explain why ?
Private Sub CommandButton2_Click()
Dim i As Long
Dim MaxI As Long
Dim Counter As Long

MaxI = 100
For i = 1 To MaxI
MaxI = MaxI + 1
Counter = Counter + 1
Next i
Debug.Print Counter

End Sub

The answer is to loop backwards:
For lngIndex = .UsedRange.Rows.Count to 3 Step -1
and insert after you.

Or change to a Do Until <You Reach the end>
depending on how/where your inserts are going.

You can change the number of loops you make with:
For Each Cell In Range("rngData")
but you need be careful you can actually reach the end of the range

NickHK

"MattShoreson" <[email protected]>
wrote in message
news:[email protected]...
>
> Code is as follows:
>
> lngLoopLength = .UsedRange.Rows.Count
>
> For lngIndex = 3 To lngLoopLength
>
> "if condition met then
> InsertRow
> end if
>
> lngLoopLength = .UsedRange.Rows.Count
>
> Next lngIndex
>
> the loop if not resizing using this method.
> Ideas anyone?
> tia,
> Matt.
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=572120
>

[MattShoreson]

Nick - not too sure what you mean in your post.

My reasoning by my post is that...

instead of moving forward through the loop and then resizing it to ensure that all cells in the usedrange are covered (they increase due to the insertrow method).

Set the looplength to start with and process from the end to the start, thereby making redundant the need to resize with inserting of rows.

what may not be apparent from the orginal post is the inserting of rows is directly relational to the position in the loop.
i.e. .Cells(lngLoopLength + 1, 1).EntireRow.Insert shift:=xlDown

I think this makes sense!

[NickHK]

Matt,
I was just trying to show that using a For i=1 to SomeVariable structure,
you can't change the number iterations once you begin. Which is what were
trying to do.
You have to use another method, either by allowing for the change in range
size or organize your code so it is not a concern.
As you have discovered.

NickHK

"MattShoreson" <[email protected]>
wrote in message
news:[email protected]...
>
> Nick - not too sure what you mean in your post.
>
> My reasoning by my post is that...
>
> instead of moving forward through the loop and then resizing it to
> ensure that all cells in the usedrange are covered (they increase due
> to the insertrow method).
>
> Set the looplength to start with and process from the end to the start,
> thereby making redundant the need to resize with inserting of rows.
>
> what may not be apparent from the orginal post is the inserting of rows
> is directly relational to the position in the loop.
> i.e. .Cells(lngLoopLength + 1, 1).EntireRow.Insert shift:=xlDown
>
> I think this makes sense!
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=572120
>