View Full Version : Solved: delete rows
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
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
...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
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
:beerchug: Glad to help and you are most welcome:)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.