Slowing down Loop process

[RMoore2764]

I am running into a problem I have never seen before when using a Loo
process. My macro is looping thru approx 1000 rows and if criteria i
correct it deletes the row. However it appears to miss several row
that should be deleted but aren't. I have to run loop several time
(up to 10) to get all. My assumption is that if loop is slowed dow
all would be picked up. Any thoughts or ideas would be greatl
appreciated. Here is code I use:

Row = 2
Do Until (Cells(Row, 2).Value = "")
Sheets("PHO").Cells(Row, 1).Select
If (Sheets("PHO").Cells(Row, 1).Value <> "8") Then
Selection.EntireRow.Delete
End If
Row = Row + 1
Loop


Thanks,
Ro

 

[Jim Cone]

Rob,

Start at the bottom of the range and work your way up.
Excel can keep track of the rows that way.

PS... You do not have to select a range to do something to it.
Cells(lngRow,1).EntireRow.Delete is more efficient.
(avoid naming variables with a term that Excel uses..."row")

Regards,
Jim Cone
San Francisco, CA

 

[William]

Hi Rob

Sub test()
Application.ScreenUpdating = False
Dim r As Range, rr As Range, c As Range
With Sheets("PHO")
Set r = .Range(.Range("A2"), _
..Range("A" & Rows.Count).End(xlUp))
For Each c In r
If c < 8 Then
If Not rr Is Nothing Then
Set rr = Union(rr, c)
Else
Set rr = c
End If
End If
Next c
If Not rr Is Nothing Then rr.EntireRow.Delete
End With
Application.ScreenUpdating = True
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| I am running into a problem I have never seen before when using a Loop
| process. My macro is looping thru approx 1000 rows and if criteria is
| correct it deletes the row. However it appears to miss several rows
| that should be deleted but aren't. I have to run loop several times
| (up to 10) to get all. My assumption is that if loop is slowed down
| all would be picked up. Any thoughts or ideas would be greatly
| appreciated. Here is code I use:
|
| Row = 2
| Do Until (Cells(Row, 2).Value = "")
| Sheets("PHO").Cells(Row, 1).Select
| If (Sheets("PHO").Cells(Row, 1).Value <> "8") Then
| Selection.EntireRow.Delete
| End If
| Row = Row + 1
| Loop
|
|
| Thanks,
| Rob
|
|
| ---
| Message posted
|

 

[JE McGimpsey]

Computers in general are not like overworked people - they don't miss
things because they go too fast.

The problem is that when, say, Row (which is a bad choice of variable
names - it will confuse the heck out of humans) is, say equal to 12,
then deleting row 12 moves row 13 up one row. Your code then looks at
the next row, which is row 13. Therefor the old row 13 never gets looked
at.

Two (of many) alternatives:

Public SUb DeleteAllBut8()
Dim i As Long
With Sheets("PHO")
For i = .Range("B" & .Rows.Count).End(xlUp).Row to 2 Step -1
If .Cells(i, 1).Value <> 8 Then _
.Cells(i, 1).EntireRow.Delete
Next i
End With
End Sub

or

Public Sub DeleteAllBut8()
Dim rCell As Range
Dim rDelete As Range
With Sheets("PHO")
For Each rCell in .Range("A2:A & _
.Range("B" & .Rows.Count).End(xlUp).Row)
If rCell.Value <> 8 Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End if
End If
Next rCell
End With
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

 

[Paul Robinson]

Hi
The more likely problem is that you are running forward through the
loop which messes up your Row numbering.
e.g. Suppose you have 3 rows in your range with an 8 on rows 2 and 3
and a "" on row 4 (your stopping condition). You get to row 2 and
delete it. Row 3 NOW BECOMES ROW 2. The loop then gets to row 3 which
has "" in it, so stops. Now you look at your range and still see an 8.

Search in this newsgroup for "deleting rows" - it's a very common
question. You will either have to loop through the range backwards
from the row with "" or, more efficiently, use Autofilter.

regards
Paul

 

[onedaywhen]

You should not increment the row counter if you have just deleted a row! Try this:

Option Explicit

Sub test()
Dim rngTemp As Excel.Range
Dim Row As Long
Row = 2
With Worksheets("PHO")
Set rngTemp = .Cells(Row, 2)
Do Until Len(rngTemp.Value) = 0
If CStr(rngTemp.Offset(, -1).Value) = "8" Then
Row = Row + 1
Else
rngTemp.EntireRow.Delete xlShiftUp
End If
Set rngTemp = .Cells(Row, 2)
Loop
End With
End Sub