Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code - not working - Why?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code - not working - Why?
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code - not working - Why?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code - not working - Why?
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 |
#6
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Code Is Not Working | Excel Discussion (Misc queries) | |||
Code not working | Excel Programming | |||
Code not working and can't see why | Excel Discussion (Misc queries) | |||
why this code not working | Excel Programming | |||
For Each Code Not Working | Excel Programming |