Solved: delete rows

[kroz]

Is there a special reason for this code not to work? It's been driving me nuts :(


Sub macro4()
Dim rRow, Nrow As Long
Dim oneCell As Range
rRow = Range("A1").End(xlDown).Row

For Each oneCell In Range("a1", Cells(rRow, 1)).Cells
If Left(oneCell.Value, 1) <> "S" Then
oneCell.EntireRow.Delete
End If
Next
End Sub


If i have 3 rows that don't start with "S" the second row is not deleted.
I think it skips the row that it deletes. so if i delete row 5 it will go to 6 automatically

[kroz]

i found an alternate that works but that still doesn't explain why the initial approach doesn't work.


Sub macro4()
Dim rRow As Long
Dim i As Integer
rRow = Range("A1").End(xlDown).Row
i = 1
While (Cells(i, 1) <> "")
If Left(Cells(i, 1).Value, 1) <> "S" Then
Cells(i, 1).EntireRow.Delete
Else
i = i + 1
End If
Wend
End Sub

[GTO]

...If i have 3 rows that don't start with "S" the second row is not deleted.
I think it skips the row that it deletes. so if i delete row 5 it will go to 6 automatically

Greetings,

To see what's happening, reduce VBIDE so you can see the sheet and step through the code using F8. You will see that as you are looping from low to high (numerically), as a row is deleted, the row that was below it, is now moved up. Thus, this row is skipped past.

Then try the second macro to see how going from the bottom rows upwardly works better.


Option Explicit

Sub macro4()
Dim rRow, Nrow As Long
Dim OneCell As Range
rRow = Range("A1").End(xlDown).Row

For Each OneCell In Range("a1", Cells(rRow, 1)).Cells
If Left(OneCell.Value, 1) <> "S" Then
MsgBox OneCell.Row
OneCell.EntireRow.Delete
End If
Next
End Sub

Sub exa()
'// Note that each variable must have ...As (type) after it, or it will be a Variant //
Dim lRow As Long, Nrow As Long
Dim OneCell As Range

lRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For Nrow = lRow To 1 Step -1
If Left(Cells(Nrow, "A"), 1) <> "S" Then
Cells(Nrow, "A").Rows.Delete
End If
Next

End Sub

Hope that helps,

Mark

[kroz]

that works much better, now i understand what's going on. Thanx Mark
By the way, didn't know the thing with the variables, thanx

[GTO]

:beerchug: Glad to help and you are most welcome:)