How to continue loop after deleting row?

[joeu2004@hotmail.com]

I have the following loop:

for each cell in selection ' n-by-m area
if 'condition based on cell.value' then
cell.entireRow.delete
else
' process cell
end if
next

As written, if the range is A1:B10 and A2 meets the
condition, the loop continues by processing (the new) B2
after deleting row 2 -- which means that the new A2 is
never processed.

Is there an easy way to continue the loop by processing
(the new) A2 after deleting row 2?

I work around the problem by adding "goto again" after
the entireRow.delete operation, where "again" is above
the for-each statement. That is, I restart the for-loop.

That is okay for now because with my data, I only delete
one row near the top of the selection. Thus, I lose little
efficiency. But I would like to know the "right" way to do
this, for the future.

(I tried putting "again" at the top of loop just after the
for-each statement, but I got a debug error because
"cell" is apparently undefined after the deletion.)

[Bob Phillips]

lr = rows(selection.row+selection.rows.count-1)
for i = lr to selection.row step -1
For j = 1 To Cels(i,Columns.Count).End(xlToLeft).Column
if cells(i,j).Value = 'condition based on cell.value' then
cells(i,j).entireRow.delete
else
' process cells(i,j)
end if
Next j
next i

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> wrote
in message news:C277EB58-6123-4FA3-AA4B-550C5CF8206C@microsoft.com...
> I have the following loop:
>
> for each cell in selection ' n-by-m area
> if 'condition based on cell.value' then
> cell.entireRow.delete
> else
> ' process cell
> end if
> next
>
> As written, if the range is A1:B10 and A2 meets the
> condition, the loop continues by processing (the new) B2
> after deleting row 2 -- which means that the new A2 is
> never processed.
>
> Is there an easy way to continue the loop by processing
> (the new) A2 after deleting row 2?
>
> I work around the problem by adding "goto again" after
> the entireRow.delete operation, where "again" is above
> the for-each statement. That is, I restart the for-loop.
>
> That is okay for now because with my data, I only delete
> one row near the top of the selection. Thus, I lose little
> efficiency. But I would like to know the "right" way to do
> this, for the future.
>
> (I tried putting "again" at the top of loop just after the
> for-each statement, but I got a debug error because
> "cell" is apparently undefined after the deletion.)

[joeu2004@hotmail.com]

Bob Phillips wrote:
> lr = rows(selection.row+selection.rows.count-1)
> for i = lr to selection.row step -1
> For j = 1 To Cels(i,Columns.Count).End(xlToLeft).Column
> if cells(i,j).Value = 'condition based on cell.value' then
> cells(i,j).entireRow.delete

Thanks. That is close. But cells(i,columns.count) refers
to the last non-empty cell in the row, not the last cell of in
the selection, as I would want. So I think I should iterate
j from 1 to "lc", whose value is:

lc = columns(selection.column + selection.columns.count - 1).column

Although your suggestion is sufficient for my immediate
problem (thanks), it does not exactly answer my question.

I wrote:
> for each cell in selection ' n-by-m area
> if 'condition based on cell.value' then
> cell.entireRow.delete
> else
> ' process cell
> end if
> next
> [....]
> Is there an easy way to continue the loop by processing
> (the new) A2 after deleting row 2?

You suggest processing the selection in reverse order,
from highest to lowest row number. I agree that would be
most efficient, and it would work in cases where processing
order does not matter.

But there are cases where the rows must be processed
from lowest to highest number.

So I am still interested in a solution that addresses my
original question as stated.

[Bob Phillips]

So you are now calculating lc and using that?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

<joeu2004@hotmail.com> wrote in message
news:1144646320.722464.28790@z34g2000cwc.googlegroups.com...
> Bob Phillips wrote:
> > lr = rows(selection.row+selection.rows.count-1)
> > for i = lr to selection.row step -1
> > For j = 1 To Cels(i,Columns.Count).End(xlToLeft).Column
> > if cells(i,j).Value = 'condition based on cell.value' then
> > cells(i,j).entireRow.delete
>
> Thanks. That is close. But cells(i,columns.count) refers
> to the last non-empty cell in the row, not the last cell of in
> the selection, as I would want. So I think I should iterate
> j from 1 to "lc", whose value is:
>
> lc = columns(selection.column + selection.columns.count - 1).column
>
> Although your suggestion is sufficient for my immediate
> problem (thanks), it does not exactly answer my question.
>
> I wrote:
> > for each cell in selection ' n-by-m area
> > if 'condition based on cell.value' then
> > cell.entireRow.delete
> > else
> > ' process cell
> > end if
> > next
> > [....]
> > Is there an easy way to continue the loop by processing
> > (the new) A2 after deleting row 2?
>
> You suggest processing the selection in reverse order,
> from highest to lowest row number. I agree that would be
> most efficient, and it would work in cases where processing
> order does not matter.
>
> But there are cases where the rows must be processed
> from lowest to highest number.
>
> So I am still interested in a solution that addresses my
> original question as stated.
>

[Dave Peterson]

One way is to build a range to delete when you're done. But then you have to
adjust that range so that you can do the delete:

Option Explicit
Sub testme01()
Dim DelRng As Range
Dim myRng As Range
Dim myCell As Range

With ActiveSheet
Set myRng = .Range("a1:c9") 'selection

For Each myCell In myRng.Cells
If myCell.Value = "a" Then
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
Next myCell

If DelRng Is Nothing Then
'do nothing
Else
Intersect(DelRng.EntireRow, .Columns(1)).EntireRow.Delete
End If
End With

End Sub

====
If you did want to loop through the rows/columns, then this worked ok for me:

Option Explicit
Sub testme02()
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iRow As Long
Dim iCol As Long
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("f11:h19") 'selection

With myRng
FirstRow = .Row
LastRow = .Rows(.Rows.Count).Row
FirstCol = .Column
LastCol = .Columns(.Columns.Count).Column
End With

For iRow = LastRow To FirstRow Step -1
For iCol = FirstCol To LastCol
If .Cells(iRow, iCol).Value = "a" Then
.Rows(iRow).Delete
Exit For 'delete that row
End If
Next iCol
Next iRow
End With
End Sub




joeu2004@hotmail.com wrote:
>
> I have the following loop:
>
> for each cell in selection ' n-by-m area
> if 'condition based on cell.value' then
> cell.entireRow.delete
> else
> ' process cell
> end if
> next
>
> As written, if the range is A1:B10 and A2 meets the
> condition, the loop continues by processing (the new) B2
> after deleting row 2 -- which means that the new A2 is
> never processed.
>
> Is there an easy way to continue the loop by processing
> (the new) A2 after deleting row 2?
>
> I work around the problem by adding "goto again" after
> the entireRow.delete operation, where "again" is above
> the for-each statement. That is, I restart the for-loop.
>
> That is okay for now because with my data, I only delete
> one row near the top of the selection. Thus, I lose little
> efficiency. But I would like to know the "right" way to do
> this, for the future.
>
> (I tried putting "again" at the top of loop just after the
> for-each statement, but I got a debug error because
> "cell" is apparently undefined after the deletion.)

--

Dave Peterson