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

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
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?

Code:
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
I would do it like this
Code:
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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.
Code:
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

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
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.

Code:
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
As long as I see your code, it should not delete row1 in any case.

Are you sure that 1st row was deleted?
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
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

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
This is how my sheet begins:
Book1 (version 1)recovered.xls
CDEF
1
2C32NIUBOABOOTSGRLWHITE/GUM7.5
3C32NIUBOABOOTSGRLWHITE/GUM8.5
4P32PROSPECTBOABOOTS06BLACK/GUM
5C32PROSPECTBOABOOTS06BLACK/GUM9
6P32PROSPECTBOOTS06BLACK/GUM
7C32PROSPECTBOOTS06BLACK/GUM10
8C32PROSPECTBOOTS06BLACK/GUM8
9C32PROSPECTBOOTS06BLACK/GUM9
10C32PROSPECTBOOTS06BLACK/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
CDEF
1C32PROSPECTBOOTS06BLACK/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
 

Forum statistics

Threads
1,181,056
Messages
5,927,865
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top