VBA makes looping easy. For example, you can use For..Next or For Each..Next when you know the number of times you want the
loop to execute. When you delete cells in a loop, however, you can run into some problems. This post shows the wrong way and
the right way to do it.
Start with a list of names in column A.
Now write code to loop through these entries and delete every row where the name begins with ‘J’. I’m a big fan of For
Each..Next loops, so we’ll give that a shot.
Sub DeleteNs1()
Dim rCell As Range
Dim rRng As Range
Dim Sh As Worksheet
Set Sh = ActiveSheet
Set rRng = Sh.Range("A1", Sh.Range("A1").End(xlDown))
For Each rCell In rRng.Cells
Debug.Print rCell.Address
If Left(rCell.Value, 1) = "J" Then
rCell.EntireRow.Delete
End If
Next rCell
End Sub
and the result
Not too good. The Debug.Print was put in the code to help us see what was going on. If you run the code, you’ll see that it
only goes to A7 instead of A9. To illustrate why the code is missing some entries, let’s look at the first entry. The code
tests ‘John’ and deletes the row. At that point James becomes Row 1, but the loop is on A2. Now it tests a2 (which is now
‘Dick’) and James is never tested or deleted.
To avoid this problem, use a For..Next loop and loop from the bottom up (right to left if you’re deleting columns).
Sub DeleteNs2()
Dim i As Long
Dim rRng As Range
Dim Sh As Worksheet
Set Sh = ActiveSheet
Set rRng = Sh.Range("A1", Sh.Range("A1").End(xlDown))
For i = rRng.Count To 1 Step -1
Debug.Print rRng(i).Address
If Left(rRng(i).Value, 1) = "J" Then
rRng(i).EntireRow.Delete
End If
Next i
End Sub
Yippee, the corect result. If you look at the Immediate Window, you’ll see that A9 through A1 was tested.
Consider this: What if the information you have has several blank lines in it? The code as posted would go to the bottom of the FIRST group, and ignore everything else. The following modification corrects that (it goes to the BOTTOM of the range, and comes back up to the last cell):
Sub DeleteNs2()
Dim i As Long
Dim rRng As Range
Dim Sh As Worksheet
Set Sh = ActiveSheet
Set rRng = Sh.Range(“A1:A”, &Sh.Range(“A65535?).End(xlUp))
For i = rRng.Count To 1 Step -1
Debug.Print rRng(i).Address
If Left(rRng(i).Value, 1) = “J” Then
rRng(i).EntireRow.Delete
End If
Next i
End Sub
Philem
You are correct, although I think you need a .Row in there. If I’m going bottom up, I usually do it this way
Set rRng = Sh.Range(“A1?,Sh.Range(“A” & Sh.Rows.Count).End(xlUp))
It eliminates the magic 655536 number.
I cant get this to work. I do not know why but it keeps giving me an error that states
Expected list seperator or )
this is on this line.
Set rRng = Sh.Range(“A1?,Sh.Range(“A” & Sh.Rows.Count).End(xlUp))
I would really appreciate help.
thanks
Felipe,
This site plays games with code in comments.
Quotes, Ampersands and other magic sequence of chars get transformed.
You have to replace them with html replacement:
Quote: ” "
Ampersand: & &
Less Than: < <
Greater Than: > >
For your question, try this instead:
Set rRng = Sh.Range(“A1”,Sh.Range(“A” & Sh.Rows.Count).End(xlUp))
Rob
To All:
Seems Microsoft changed the behavior starting with 2002.
How to delete cells by using a “For Each…Next” loop
http://support.microsoft.com/kb/291300
TomW