I have the block of code below in a Sub that tidies up a copied worksheet
by removing unused rows. Its contained in a With €¦ End With block that
refers to the copied sheet. Id like to know how the Exit For statement is
handled if its in a For ... Next block that is nested within another For
€¦ Next block. I.e. will it cause the code to exit only the inner For €¦
Next block and proceed with the

If VacRowClear = True Then

statement, or will it exit the outer For €¦ Next block?

I use nested For €¦ Next blocks quite a lot in my code; are they considered
bad programming practice?

'delete Vacancy block rows if clear
If VacancyStartRow < VacancyEndRow Then
For r = VacancyEndRow To VacancyStartRow Step -1
'assume clear
VacRowClear = True
'test whether first 9 cells are all clear
For c = 1 To 9
If .Cells(r, c).Value < "" Then 'if any cell not clear....
VacRowClear = False ' ... reset Boolean
Exit For €˜<---- will this statement exit only the inner
€˜ For ... Next block, or the outer one as well?
End If
Next c
'if all 9 first cells are clear, delete row
If VacRowClear = True Then
.Rows(r).Delete
End If
Next r
End If

Nope, it'll just exit the innermost For/next loop.

I like to use a variable that I can set and check.

Dim LeaveNow as boolean

leavenow = false
for i = 1 to 10
msgbox "hi" there"
for j = 22 to 25
if j = 24 then
leavenow = true
exit for 'leave the j loop
end if
next j
if leavenow = true then
exit for 'leave the i loop
end if
'else continue with the i loop code
next j

IanKR wrote:

I have the block of code below in a Sub that tidies up a copied worksheet
by removing unused rows. Its contained in a With €¦ End With block that
refers to the copied sheet. Id like to know how the Exit For statement is
handled if its in a For ... Next block that is nested within another For
€¦ Next block. I.e. will it cause the code to exit only the inner For €¦
Next block and proceed with the

If VacRowClear = True Then

statement, or will it exit the outer For €¦ Next block?

I use nested For €¦ Next blocks quite a lot in my code; are they considered
bad programming practice?

'delete Vacancy block rows if clear
If VacancyStartRow < VacancyEndRow Then
For r = VacancyEndRow To VacancyStartRow Step -1
'assume clear
VacRowClear = True
'test whether first 9 cells are all clear
For c = 1 To 9
If .Cells(r, c).Value < "" Then 'if any cell not clear...
VacRowClear = False ' ... reset Boolean
Exit For €˜<---- will this statement exit only the inner
€˜ For ... Next block, or the outer one as well?
End If
Next c
'if all 9 first cells are clear, delete row
If VacRowClear = True Then
.Rows(r).Delete
End If
Next r
End If

--

Dave Peterson

Ps. I was kind of terse in my initial response.

Exiting a For/next loop only exits that loop--it won't leave the higher level
loop.

I don't think that there's anything wrong with nesting these. Sometimes, the
code can get complex, so instead of having lots of code within a for/next loop,
I'll call a sub or function and put that code in there.

It makes it a little easier when debugging the code--well, for me at least.



IanKR wrote:

I have the block of code below in a Sub that tidies up a copied worksheet
by removing unused rows. Its contained in a With €¦ End With block that
refers to the copied sheet. Id like to know how the Exit For statement is
handled if its in a For ... Next block that is nested within another For
€¦ Next block. I.e. will it cause the code to exit only the inner For €¦
Next block and proceed with the

If VacRowClear = True Then

statement, or will it exit the outer For €¦ Next block?

I use nested For €¦ Next blocks quite a lot in my code; are they considered
bad programming practice?

'delete Vacancy block rows if clear
If VacancyStartRow < VacancyEndRow Then
For r = VacancyEndRow To VacancyStartRow Step -1
'assume clear
VacRowClear = True
'test whether first 9 cells are all clear
For c = 1 To 9
If .Cells(r, c).Value < "" Then 'if any cell not clear...
VacRowClear = False ' ... reset Boolean
Exit For €˜<---- will this statement exit only the inner
€˜ For ... Next block, or the outer one as well?
End If
Next c
'if all 9 first cells are clear, delete row
If VacRowClear = True Then
.Rows(r).Delete
End If
Next r
End If

--

Dave Peterson

On Thu, 03 Dec 2009 23:35:29 -0000, Dave Peterson
wrote:

IanKR wrote:

I have the block of code below in a Sub that tidies up a copied
worksheet
by removing unused rows. It€„¢s contained in a With €¦ End With block
that
refers to the copied sheet. I€„¢d like to know how the Exit For
statement is
handled if it€„¢s in a For ... Next block that is nested within another
For
€¦ Next block. I.e. will it cause the code to exit only the inner For
€¦
Next block and proceed with the

If VacRowClear = True Then

statement, or will it exit the outer For €¦ Next block?

I use nested For €¦ Next blocks quite a lot in my code; are they
considered
bad programming practice?

'delete Vacancy block rows if clear
If VacancyStartRow < VacancyEndRow Then
For r = VacancyEndRow To VacancyStartRow Step -1
'assume clear
VacRowClear = True
'test whether first 9 cells are all clear
For c = 1 To 9
If .Cells(r, c).Value < "" Then 'if any cell not
clear...
VacRowClear = False ' ... reset Boolean
Exit For €˜<---- will this statement exit only the
inner
€˜ For ... Next block, or the outer one as
well?
End If
Next c
'if all 9 first cells are clear, delete row
If VacRowClear = True Then
.Rows(r).Delete
End If
Next r
End If



Ps. I was kind of terse in my initial response.

Exiting a For/next loop only exits that loop--it won't leave the higher
level
loop.

I don't think that there's anything wrong with nesting these.
Sometimes, the
code can get complex, so instead of having lots of code within a
for/next loop,
I'll call a sub or function and put that code in there.

It makes it a little easier when debugging the code--well, for me at
least.




Many thanks, Dave.
Ian