I have the block of code below in a Sub that tidies up a copied workshee=
t  =

by removing unused rows. It=E2=80=99s contained in a With =E2=80=A6 End =
With block that  =

refers to the copied sheet. I=E2=80=99d like to know how the Exit For st=
atement is  =

handled if it=E2=80=99s in a For ... Next block that is nested within an=
other For  =

=E2=80=A6 Next block. I.e. will it cause the code to exit only the inner=
 For =E2=80=A6  =

Next block and proceed with the

	If VacRowClear =3D True Then

statement, or will it exit the outer For =E2=80=A6 Next block?

I use nested For =E2=80=A6 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 =3D VacancyEndRow To VacancyStartRow Step -1
         'assume clear
         VacRowClear =3D True
         'test whether first 9 cells are all clear
         For c =3D 1 To 9
             If .Cells(r, c).Value <> "" Then    'if any cell not clear.=
...
                 VacRowClear =3D False             ' ... reset Boolean
                 Exit For  =E2=80=98<---- will this statement exit only =
the inner
                           =E2=80=98 For ... Next block, or the outer on=
e as well?
             End If
         Next c
         'if all 9 first cells are clear, delete row
         If VacRowClear =3D 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. 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

-- 

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. 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

-- 

Dave Peterson

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

<petersod@verizonxspam.net> wrote:

> IanKR wrote:
>>
>> I have the block of code below in a Sub that tidies up a copied  =

>> worksheet
>> by removing unused rows. It=C3=A2=E2=82=AC=E2=84=A2s contained in a W=
ith =C3=A2=E2=82=AC=C2=A6 End With block  =

>> that
>> refers to the copied sheet. I=C3=A2=E2=82=AC=E2=84=A2d like to know h=
ow the Exit For  =

>> statement is
>> handled if it=C3=A2=E2=82=AC=E2=84=A2s in a For ... Next block that i=
s nested within another  =

>> For
>> =C3=A2=E2=82=AC=C2=A6 Next block. I.e. will it cause the code to exit=
 only the inner For  =

>> =C3=A2=E2=82=AC=C2=A6
>> Next block and proceed with the
>>
>>         If VacRowClear =3D True Then
>>
>> statement, or will it exit the outer For =C3=A2=E2=82=AC=C2=A6 Next b=
lock?
>>
>> I use nested For =C3=A2=E2=82=AC=C2=A6 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 =3D VacancyEndRow To VacancyStartRow Step -1
>>          'assume clear
>>          VacRowClear =3D True
>>          'test whether first 9 cells are all clear
>>          For c =3D 1 To 9
>>              If .Cells(r, c).Value <> "" Then    'if any cell not  =

>> clear...
>>                  VacRowClear =3D False             ' ... reset Boolea=
n
>>                  Exit For  =C3=A2=E2=82=AC=CB=9C<---- will this state=
ment exit only the  =

>> inner
>>                            =C3=A2=E2=82=AC=CB=9C For ... Next block, =
or the outer one as  =

>> well?
>>              End If
>>          Next c
>>          'if all 9 first cells are clear, delete row
>>          If VacRowClear =3D 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 highe=
r  =

> 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