Deleting rows

[Greg Snidow]

Greetings all. I am trying to delete some rows based on whether or not the
right 4 characters in the cell = "View". I am using the following...

LstRow = [B5000].End(xlUp).Row
Set Myrng = Range("B1:B" & LstRow)
For Each MyCell In Myrng
If Right(MyCell, 4) = "View" Then
Rows(MyCell.Row).EntireRow.Delete
End If
Next MyCell

The problem is that I have to run it multiple times to delete all the rows
ending in "View". In column B there are 8 rows of records ending in "View",
then three records not ending in "View", for a total of 11 records per id
value in column A, and this pattern is continuous all the way to around row
4000 or so. When I run it the first time, it deletes the first three rows in
each group. The second time I run it, it deletes the next two records from
each group, and then I have to run it three more times to delete the last
three records from each group. Any ideas why it is doing this? Thank you.

Greg

 

[meh2030]

Greetings all.  I am trying to delete some rows based on whether or notthe
right 4 characters in the cell = "View".  I am using the following...

    LstRow = [B5000].End(xlUp).Row
    Set Myrng = Range("B1:B" & LstRow)
    For Each MyCell In Myrng
        If Right(MyCell, 4) = "View" Then
            Rows(MyCell.Row).EntireRow.Delete
        End If
    Next MyCell

The problem is that I have to run it multiple times to delete all the rows
ending in "View".  In column B there are 8 rows of records ending in "View",
then three records not ending in "View", for a total of 11 records per id
value in column A, and this pattern is continuous all the way to around row
4000 or so.  When I run it the first time, it deletes the first three rows in
each group.  The second time I run it, it deletes the next two records from
each group, and then I have to run it three more times to delete the last
three records from each group.  Any ideas why it is doing this?  Thank you.

Greg

Greg,

As you delete rows, your range changes each time a row is deleted.
Thus as you cycle from top to bottom, you never actually reach the
bottom. (See the RowDeletion procedure below. Run this procedure via
F8 repeatedly, and watch the Immediate Window. You can also put dummy
data (such as 1, 2, 3, 4, 5, respectively into the rows in column A in
a worksheet and watch both Excel and the Immediate window as you press
F8). Use a For Loop with Step - 1 rather than the For Each Loop.
(See RowDeletionStep1 below). I hope this helps.

Best,

Matthew Herbert

Sub RowDeletion()
Dim Rng As Range
Dim rngCell As Range

Set Rng = Range("A1:A5")

For Each rngCell In Rng.Cells
Debug.Print "Eval. Cell :"; rngCell.Address
Debug.Print "Pre-deletion :"; Rng.Address
rngCell.EntireRow.Delete
Debug.Print "Post-deletion:"; Rng.Address
Next
End Sub

Sub RowDeletionStep1()
Dim lngLastRow As Long
Dim lngFirstRow As Long
Dim lngL As Long

lngLastRow = Range("B5000").End(xlUp).Row
lngFirstRow = 1

For lngL = lngLastRow To lngFirstRow Step -1
If Right(Range("B" & lngL), 4) = "View" Then
Range("B" & lngL).EntireRow.Delete
End If
Next

End Sub

 

[Don Guillett]

sub deleteifview()
for i=cells(rows.count,"b").End(xlUp).Row to 2 step -1
if ucase(right(cells(i,4),4))="VIEW" then rows(i).delete
next i
end sub

 

[Greg Snidow]

Thank you so much Mathew for the quick response. I never thought of starting
at the bottom, but now it make sense.

Greg

Greetings all. I am trying to delete some rows based on whether or not the
right 4 characters in the cell = "View". I am using the following...

LstRow = [B5000].End(xlUp).Row
Set Myrng = Range("B1:B" & LstRow)
For Each MyCell In Myrng
If Right(MyCell, 4) = "View" Then
Rows(MyCell.Row).EntireRow.Delete
End If
Next MyCell

The problem is that I have to run it multiple times to delete all the rows
ending in "View". In column B there are 8 rows of records ending in "View",
then three records not ending in "View", for a total of 11 records per id
value in column A, and this pattern is continuous all the way to around row
4000 or so. When I run it the first time, it deletes the first three rows in
each group. The second time I run it, it deletes the next two records from
each group, and then I have to run it three more times to delete the last
three records from each group. Any ideas why it is doing this? Thank you.

Greg

Greg,

As you delete rows, your range changes each time a row is deleted.
Thus as you cycle from top to bottom, you never actually reach the
bottom. (See the RowDeletion procedure below. Run this procedure via
F8 repeatedly, and watch the Immediate Window. You can also put dummy
data (such as 1, 2, 3, 4, 5, respectively into the rows in column A in
a worksheet and watch both Excel and the Immediate window as you press
F8). Use a For Loop with Step - 1 rather than the For Each Loop.
(See RowDeletionStep1 below). I hope this helps.

Best,

Matthew Herbert

Sub RowDeletion()
Dim Rng As Range
Dim rngCell As Range

Set Rng = Range("A1:A5")

For Each rngCell In Rng.Cells
Debug.Print "Eval. Cell :"; rngCell.Address
Debug.Print "Pre-deletion :"; Rng.Address
rngCell.EntireRow.Delete
Debug.Print "Post-deletion:"; Rng.Address
Next
End Sub

Sub RowDeletionStep1()
Dim lngLastRow As Long
Dim lngFirstRow As Long
Dim lngL As Long

lngLastRow = Range("B5000").End(xlUp).Row
lngFirstRow = 1

For lngL = lngLastRow To lngFirstRow Step -1
If Right(Range("B" & lngL), 4) = "View" Then
Range("B" & lngL).EntireRow.Delete
End If
Next

End Sub