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]

Before you run the sample I posted, I would "comment out" the last line:

oDelete.Delete

and add:

oDelete.Interior.ColorIndex = 3

You'll be able to see the rows that have been targeted for deletion and make sure it's right before you actually let it delete them in a subsequent execution.

Gary

 

[Gary McMaster]

In case you hadn't noticed, I forgot to include "EntireRow" in the previous "Union" example.

Sub PrepareForMTRMB()

'Prepare list for NTRMB
'Range NTRMBS starts as !$D$34:$D$58
Dim C As Range
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.EntireRow)
    Else
        Set oDelete = C.EntireRow
    End If
Else

End If

Next C

'oDelete.Delete
oDelete.Interior.ColorIndex = 3

End Sub

The thread below is one of the more recent examples of using For Next and working from the bottom up. There are lots of others too.

http://www.mrexcel.com/forum/showthread.php?t=578758

 

[Mister H]

Hi Gary:

THANKS for the updated code. I hadn't noticed that omission but I just tried your Revised Code and it works GREAT

THANKS for the suggestion regarding highlighting the rows instead of deleting them. That really helped a lot

THANKS for the link. I will check it out when I get a few minutes of free time.

Your help and expertise was GREATLY Appreciated.

Have a GREAT day,
Mark

 

[Mister H]

Not quite working

Hi Gary or anyone that can assist

I ran into a little problem. I need to alter one line of code that you gave me. I need the code to include blanks. So:

If C.Value = "To be entered by MAG" OR IS BLANK Then (mark it for deletion).

I tried doing another macro using your code but it error out on me if there is nothing to delete (eg NO Blanks). Here is what I tried:

Sub DeleteBlanks()
'Prepare list for NTRMB
'Range NTRMBS starts as !$D$34:$D$58
Dim C As Range
Dim oDelete As Range
For Each C In Range("NTRMBS")
    If C.Value = "" Then
 
    If Not oDelete Is Nothing Then
        Set oDelete = Union(oDelete, C.EntireRow)
    Else
        Set oDelete = C.EntireRow
    End If
Else
End If
Next C
oDelete.Delete
'oDelete.Interior.ColorIndex = 3
End Sub

Any guidance would be GREATLY Appreciated.

THANKS,
Mark

 

[Gary McMaster]

If C.Value = "" or C.Value = "To be entered by MAG" Then

or if there is a chance of differences in letter case:

If C.Value = "" or UCase(C.Value) = "TO BE ENTERED BY MAG" Then

If C.Value = "" will not include cells that appear to be empty but may contain a blank space

Gary

 

[Mister H]

THANKS Gary. That did the trick. Your Expertise is APPRECIATED.

Have a GREAT day,
Mark