Code - not working - Why?

[Guest]

I thought the following should work, BUT IT DOESN'T.
Can somone "point-out" WHY it doesn't? - TIA,


Sub Foo()
With Selection ' Range currently highlighted - example A3:A50
For Each cell In Selection
If .Interior.ColorIndex = 6 Then
cell.EntireRow.Delete
End If
Next cell
End With
End Sub

 

[Ron de Bruin]

Hi Jim

When you delete you always start on the bottom

Try this

Sub Foo()
Dim srow As Long
Dim erow As Long
Dim I As Long

If Selection.Columns.Count > 1 Then Exit Sub

srow = Selection.Cells(Selection.Cells.Count).Row
erow = Selection.Cells(1).Row

For I = srow To erow Step -1
If Cells(I, Selection.Column).Interior.ColorIndex = 3 Then
Cells(I, Selection.Column).EntireRow.Delete
End If
Next I
End Sub

 

[Guest]

Thanks Ron, much appreciated. I'll study over your solution.
Jim

 

[Guest]

Ron:
Thanks for the *corrected* code.
But could you tell me **at what point**
my original code "FAILS"? I need to
better understand why things don't
work, as well as why they do. Do you
mind.

I know the Selection object is a powerful tool.
And I've seen code where the For Each is
used on it (as the variable "cell" is one of
the collection of cells within);
It might be as simple as the (excel) business rule,
"when using the Selection object
you can't delete a row and continue
on within"...
But, anyway could you comment on what I'm trying to
say, here. Appr in Advance..
Jim

 

[Ron de Bruin]

Hi Jim

Select A1:A10 and make them red

When I use your macro (With a little change)you see that it not delete all cells because
you delete from row 1 till 10 and when you delete row1, row 2 will be row 1.
Then it check row 2 that is row 3 now.......................................
That's why it delete only 1,3,5,7,9

Sub Foo()
Dim cell As Range
For Each cell In Selection
If cell.Interior.ColorIndex = 3 Then
cell.EntireRow.Delete
End If
Next cell
End Sub

If you use my macro that work from the bottom to the top it have no problems with this

Sub Foo2()
Dim srow As Long
Dim erow As Long
Dim I As Long

If Selection.Columns.Count > 1 Then Exit Sub

srow = Selection.Cells(Selection.Cells.Count).Row
erow = Selection.Cells(1).Row

For I = srow To erow Step -1

If Cells(I, Selection.Column).Interior.ColorIndex = 3 Then
Cells(I, Selection.Column).EntireRow.Delete
End If
Next I
End Sub

 

[Guest]

Most helpful Ron;
Thanks a lot,
Jim

Hi Jim

Select A1:A10 and make them red

When I use your macro (With a little change)you see that it not delete all cells because
you delete from row 1 till 10 and when you delete row1, row 2 will be row 1.
Then it check row 2 that is row 3 now.......................................
That's why it delete only 1,3,5,7,9

Sub Foo()
Dim cell As Range
For Each cell In Selection
If cell.Interior.ColorIndex = 3 Then
cell.EntireRow.Delete
End If
Next cell
End Sub

If you use my macro that work from the bottom to the top it have no problems with this

Sub Foo2()
Dim srow As Long
Dim erow As Long
Dim I As Long

If Selection.Columns.Count > 1 Then Exit Sub

srow = Selection.Cells(Selection.Cells.Count).Row
erow = Selection.Cells(1).Row

For I = srow To erow Step -1

If Cells(I, Selection.Column).Interior.ColorIndex = 3 Then
Cells(I, Selection.Column).EntireRow.Delete
End If
Next I
End Sub