FOR Statement Issue
 

I'm having a problem when DELETING a row that I don't want ... it skips the
next row.

Here's the code:

Dim cell As Range
For Each cell In Worksheets("sheet1").Range("A1:A5")
If Not (cell.Text Like "Y") Then
cell.EntireRow.Delete
End If
Next

Here's the data set:

A
1 Y
2 N
3 N
4 Y

Cell A2 is deleted, but A3 is not deleted. Apparently, after deleting Cell
A2, Cell A3 assumes that position and it moves to Cell A4.

Any ideas how to resolve this?

Thanks.

Gary

FOR Statement Issue
 

Hi Gary,

One method would be to iterate through the range in reverse order:

Public Sub TesterA()
Dim cell As Range
Dim rng As Range
Dim i As Long

Set rng = Worksheets("sheet2").Range("A1:A5")

For i = rng.Cells.Count To 1 Step -1
If Not uvase(rng.Cells(i).Value) Like "Y" Then
rng.Cells(i).EntireRow.Delete
End If
Next

End Sub

Another method would be to build a deletion range and make a single deletion
when the entire range has been processed:

Public SubTesterB()
Dim rng As Range
Dim rCell As Range
Dim delRng As Range

Set rng = Worksheets("sheet2").Range("A1:A5")

For Each rCell In rng.Cells
If Not UCase(rCell.Value) Like "Y" Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

End Sub

---
Regards,
Norman



"G" wrote in message
...
I'm having a problem when DELETING a row that I don't want ... it skips
the
next row.

Here's the code:

Dim cell As Range
For Each cell In Worksheets("sheet1").Range("A1:A5")
If Not (cell.Text Like "Y") Then
cell.EntireRow.Delete
End If
Next

Here's the data set:

A
1 Y
2 N
3 N
4 Y

Cell A2 is deleted, but A3 is not deleted. Apparently, after deleting
Cell
A2, Cell A3 assumes that position and it moves to Cell A4.

Any ideas how to resolve this?

Thanks.

Gary

FOR Statement Issue
 

As Norman points out, the solution to your problem of skipping rows is to
process the cells in reverse order, using the row number as the loop counter.

I would like to make another comment: "LIKE" is a very slow operation; it's
intended for situations where you need to match a pattern, such as a cell
where, say, the 2nd letter is "Y" and it ends with the number "258", i.e.
Cell.Text Like "?Y*258".

Given your data, you should be using a simple comparison for equality, i.e.

If Cell.Text = "Y"

Putting that all together:

Set Rng = Range("A1:A5")
With Rng
For R = .Cells.Count To 1 Step = -1
If .Cells(R).Text = "Y" Then
.Cells(R).EntireRow.Delete
End If
Next R
End With

Of course when you are deleting rows, that is an even slower operation, so you
won't see a speed difference related to LIKE, but the principle is valid
nonetheless.



On Fri, 9 Sep 2005 07:53:01 -0700, "G" wrote:

I'm having a problem when DELETING a row that I don't want ... it skips the
next row.

Here's the code:

Dim cell As Range
For Each cell In Worksheets("sheet1").Range("A1:A5")
If Not (cell.Text Like "Y") Then
cell.EntireRow.Delete
End If
Next

Here's the data set:

A
1 Y
2 N
3 N
4 Y

Cell A2 is deleted, but A3 is not deleted. Apparently, after deleting Cell
A2, Cell A3 assumes that position and it moves to Cell A4.

Any ideas how to resolve this?

Thanks.

Gary