Why do I have to run these Delete Codes Twice

[Mister H]

Hi All:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Once AGAIN, I am turning to the experts for an explanation.<o></o>
<o></o>
I have altered a Change Event code that was hiding rows so that I can just run it as a normal macro (when required). I have 2 named ranges and I want the code to look in each cell of the range if if criteria is met then DELETE that row.<o></o>
<o></o>
It seems to work but I have to run the macro a couple of times in order to make sure it has deleted all rows that met the criteria. It seems to leave a few rows in when I run the macro the first time but it finds the rows and deleted them on the 2nd go. I am guessing that I have created code that is not quite understood. Can someone tell me what i am missing?<o></o>
<o></o>
Here are my 2 codes:<o></o>

[COLOR=black][FONT=Verdana]Sub PrepareForMAG()[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]'Prepare List For <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:stockticker w:st="on">MAG</st1:stockticker><o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Verdana][B]'Range <st1:stockticker w:st="on">MAGS</st1:stockticker> starts as $D$3:$D$27 <o:p></o:p>[/B][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For Each c In Range("<st1:stockticker w:st="on">MAGS</st1:stockticker>")[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]If c.Value = "Entered by NTRMB" Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]c.EntireRow.Delete[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Else[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Next c<o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]

<o></o>

Sub PrepareForMTRMB()
'Prepare list for NTRMB<o:p></o:p>
'Range NTRMBS starts as !$D$34:$D$58 <o:p></o:p>
For Each c In Range("NTRMBS")
If c.Value = "To be entered by <st1:stockticker w:st="on">MAG</st1:stockticker>" Then
c.EntireRow.Delete
Else
End If
Next c<o:p></o:p>
End Sub<o:p></o:p>

Any suggestions???

THANKS,
Mark

 

[Gary McMaster]

I believe your problem will disappear if you start at the bottom and work your way up. Unfortunately, that precludes the use of For Each.

Gary

 

[Mister H]

THANKS for the suggestions Gary.

Is that a glitch in Excel or just logical?

Any idea how (what code would you suggest) I would start at the bottom and work my way up? Does this mean I would not need to use named ranges but instead start in the last row and work up?

THANKS Again,
Take Care,
Mark

 

[Scott Huish]

It's not a glitch, and it is very logical.

If you start at the top and work down this is what happens.

Your range starts at D3

Say it deletes D4

Everything shifts up 1 row.

So now what was in D5 is now in D4. This will now not be checked because in the next iteration of your loop it will check the ACTUAL cell D5, not what used to be there (which is now in D4).

 

[Gary McMaster]

precludes the use of For Each

A bit of a mis-statement. You can use For Each but I believe you must use "Union" to create a range (collection) of rows to be deleted after the For Each loop terminates. Rows do not change position, as stated by HOTPEPPER, and you delete them all at once when the loop has finished.

Gary

 

[Scott Huish]

The values in the rows do change position if you just delete them. They would not if you used Union as you suggested, however it does use less code to do a backward For Next loop, but Union is probably quicker.

 

[Gary McMaster]

Try like this (untested).

Sub PrepareForMTRMB()

'Prepare list for NTRMB
'Range NTRMBS starts as !$D$34:$D$58
Dim oDelete As Range

For Each c In Range("NTRMBS")

    If c.Value = "To be entered by MAG" Then
    
    If Not oDelete Is Nothing Then
        Set oDelete = Union(oDelete, c)
    Else
        Set oDelete = c
    End If
Else

End If

Next c

oDelete.Delete

End Sub

 

[Mister H]

THANKS to BOTH of you for the help and explanations. That makes sense now

I have to step out of the office for now but when I return tomorrow I will google or search here for code regarding starting at the bottom and working up or using Union

Have a GREAT night and THANKS AGAIN
Mark

 

[Gary McMaster]

Rows do change position if you just delete them.

Sorry, I agree that the rows change. What I meant was the the problem you cited is avoided because you don't delete any rows until after the loop terminates.

Gary

 

[Mister H]

I just got home and noticed you two had replied again. It must have been at the same time I was sending my last message. Anyway, THANKS Again to BOTH of your for your help. I will give Gary's code a try tomorrow when I get back to the office. THANKS for the code

Bye 4 Now,
Mark