For Each Loop to Delete row w/ value (Loop Backwards)?

[DougStroud]

I am just curious if a For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up, just wondering if this is possible?

Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")

For Each c In rng
If Left(c.Value, 1) <> "~~" Then
c.EntireRow.Delete
End If
Next c

 

[DougStroud]

I just did another test, it my loop is actually deleting row #1. So it is not pushing anthing anywhere it is beginning to delete in row 1.

 

[DougStroud]

Still deletes row #1 w/ me adding your last Shift:=xlup

This has to be something easy.... I just don't see it.

Ps. I ran your version- that is BAD!, it was fast- about 3.5 seconds to evaluate 15,500 records, removing 1500 of them.

But I still want to figure out this bit on hte looping backwards.

 

[jindon]

Hummm

I don't understand why...

Do you have any event code on that sheet?

 

[DougStroud]

No. I am watching in the Locals window, i is 8 lrow is 24 and it actually deletes Row 1.
I colored it yellow just to make sure I am not mis-reading any data. It deletes the first row.... bizarre

 

[DougStroud]

Sorry J-
I should have written that i is the first row that does not have the tilde character ~
just for clarity sake.

 

[jindon]

Hummm
How about

Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
With ActiveSheet
     lrow = .Cells(Rows.Count, 3).End(xlUp).Row

     For i = lrow To 2 Step -1
          If Left(.Cells(i, 3).Value, 1) <> "~" Then .Rows(i).Delete
     Next i
End With
End Sub

 

[DougStroud]

Jindon,
I figured it out.
This line was causing the problem, why I don't know.

Cells(i).EntireRow.Delete

when I changed it to this, it behaved as expected.... I will study this.

Cells(i, 3).EntireRow.Delete

It is late here- I would like to go over your code w/ you .... maybe tomorrow or the weekend-

thanks,

Doug

 

[jindon]

Jindon,
I figured it out.
This line was causing the problem, why I don't know.

Cells(i).EntireRow.Delete

when I changed it to this, it behaved as expected.... I will study this.

Cells(i, 3).EntireRow.Delete

It is late here- I would like to go over your code w/ you .... maybe tomorrow or the weekend-

thanks,

Doug

Hahaha, Of course!

 

[DougStroud]

Does this make sense to you why it acted as it did?

 

[jindon]

OK

You can check with adding msgbox

Add
MsgBox Cells(i).Address

before deletion