Code - not working - Why?

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim May" <JimMay@discussions.microsoft.com> wrote in message news:4608514B-B791-42C7-A97C-C808CA04E218@microsoft.com...
>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

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

"Ron de Bruin" wrote:

> 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
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Jim May" <JimMay@discussions.microsoft.com> wrote in message news:4608514B-B791-42C7-A97C-C808CA04E218@microsoft.com...
> >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:
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" wrote:

> 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
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Jim May" <JimMay@discussions.microsoft.com> wrote in message news:4608514B-B791-42C7-A97C-C808CA04E218@microsoft.com...
> >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
>
>
>

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim May" <JimMay@discussions.microsoft.com> wrote in message news:C66B0208-3792-4637-8B04-2EFB39662C68@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Jim May" <JimMay@discussions.microsoft.com> wrote in message news:4608514B-B791-42C7-A97C-C808CA04E218@microsoft.com...
>> >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
>>
>>
>>

Most helpful Ron;
Thanks a lot,
Jim

"Ron de Bruin" wrote:

> 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
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Jim May" <JimMay@discussions.microsoft.com> wrote in message news:C66B0208-3792-4637-8B04-2EFB39662C68@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "Jim May" <JimMay@discussions.microsoft.com> wrote in message news:4608514B-B791-42C7-A97C-C808CA04E218@microsoft.com...
> >> >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
> >>
> >>
> >>
>
>
>