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

 

[zzjasonzz]

I would do it like this

For i = 36 To 2 Step -1
    If Range("C" & i) = "~~" Then Rows(i).EntireRow.Delete
Next i

not sure about the for each

 

[jindon]

Doug
1) You can not loop backwards using For Each Loop
2) This is what I often write to delete the row and it is fast, because it deletes multiple rows at a time, not one by one.

Sub Filterout()
Dim c As Range
Dim rng As Range
Dim txt As String
Set rng = Range("c2:c36")
Again:
For Each c In rng
     If Left(c.Value, 1) <> "~~" Then
          txt = txt & "," & c.Address(0,0)
          If Len(txt) > 245 Then
               Range(Mid$(txt,2)).EntireRow.Delete
               txt = "" : GoTo Again
          End If
     End If
Next c
If Len(txt) Then Range(Mid$(txt,2)).EntireRow.Delete
End Sub

 

[DougStroud]

Hi Jindon,
Thanks for confirming that cannot be done.
Before I use your loop- can you explain something to me?
If I use a For To loop and step backwards, it deletes all the rows as I would expect, except row 1. I start off w/ row 1 being blank, but my method pushes the rows up one after the first row is deleted.
In this case it deletes all instances except it cannot get to row 1 now.

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

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

 

[jindon]

Sorry, your question is not clear to me.

For i = lrow To 2 Step -1, means

Loop from the bottom to the 2nd row one by one, so it never go to 1st row.

Am I missing something?

 

[DougStroud]

Yes you are correct, my range begins in row 2 and the loop ends at row 2. Row 1 is blank.
So there has to be something wrong in my loop for the line that deletes the row because after the loop deletes the first row that meets the criteria, my range up into row 1 which I do not want it to do, nor will it process row 1.

 

[jindon]

As long as I see your code, it should not delete row1 in any case.

Are you sure that 1st row was deleted?

 

[DougStroud]

No row 1 does not get deleted, nor does it get evaluated, that is correct. Row 1 starts off as blank.
Values are in rows 2:36
once my code runs, and the first row is deleted it pushes my values up into row 1. This row will not get evaluated.

If this is not clear, can you run my code so you can see what I am trying to explain?

 

[DougStroud]

This is how my sheet begins:

Book1 (version 1)recovered.xls
	C	D	E	F
1
2	C32NIUBOABOOTSGRLWHITE/GUM7.5
3	C32NIUBOABOOTSGRLWHITE/GUM8.5
4	P32PROSPECTBOABOOTS06BLACK/GUM
5	C32PROSPECTBOABOOTS06BLACK/GUM9
6	P32PROSPECTBOOTS06BLACK/GUM
7	C32PROSPECTBOOTS06BLACK/GUM10
8	C32PROSPECTBOOTS06BLACK/GUM8
9	C32PROSPECTBOOTS06BLACK/GUM9
10	C32PROSPECTBOOTS06BLACK/GUM9.5
11	~P32PROSPECTBOOTS06WHITE/BLACK/GUM
12	~C32PROSPECTBOOTS06WHITE/BLACK/GUM10
13	~C32PROSPECTBOOTS06WHITE/BLACK/GUM7
14	~P32GRIFFONBOOTSGRLGREY/NAVY
15	~C32GRIFFONBOOTSGRLGREY/NAVY7.5
16	~P32LASHEDBOOTSGOLD/BLACK
17	~C32LASHEDBOOTSGOLD/BLACK9
18	~P32MINONBOOTSNAVY/LT.BLUE
19	~C32MINONBOOTSNAVY/LT.BLUE6
20	~PDVDSNOW411VIDEO#3
21	~PDVDSKATE411VMVOL.13-04
22	~PDVDSKATE411VIDEOMAGVOL.13-01
23	~P68605ORIGSMARTYPANTBLACK
Sheet3

This is it after I run my loop:
Row 1 now has a value in it because a row is deleted and this pushes my data up one row, into Row 1.

Book1 (version 1)recovered.xls
	C	D	E	F
1	C32PROSPECTBOOTS06BLACK/GUM9.5
2	~P32PROSPECTBOOTS06WHITE/BLACK/GUM
3	~C32PROSPECTBOOTS06WHITE/BLACK/GUM10
4	~C32PROSPECTBOOTS06WHITE/BLACK/GUM7
5	~P32GRIFFONBOOTSGRLGREY/NAVY
6	~C32GRIFFONBOOTSGRLGREY/NAVY7.5
7	~P32LASHEDBOOTSGOLD/BLACK
8	~C32LASHEDBOOTSGOLD/BLACK9
9	~P32MINONBOOTSNAVY/LT.BLUE
10	~C32MINONBOOTSNAVY/LT.BLUE6
11	~PDVDSNOW411VIDEO#3
12	~PDVDSKATE411VMVOL.13-04
13	~PDVDSKATE411VIDEOMAGVOL.13-01
14	~P68605ORIGSMARTYPANTBLACK
15	~C68605ORIGSMARTYPANTBLACKXS
16	~P68605ORIGSMARTYPANTKHAKI
17	~C68605ORIGSMARTYPANTKHAKIXS
18	~P68605SMARTYOPTICALJKTGREEN
19	~C68605SMARTYOPTICALJKTGREENXS
Sheet3

 

[jindon]

OK
Try adding

Cells(i).Delete shift:=xlUp