Posted to microsoft.public.excel.programming
|
|
Code - not working - Why?
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" wrote in message ...
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" wrote in message ...
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
|