Code problem

[Ruben]

I would like to delete rows in column c for a criteria with a macro code. But
I have a problem with my code, it doesn't work. Could anyone tell me what's
wrong with my macro code? Thanks!

Sub filter()
Dim i As integer
i=0
Do until i=""
'my criteria is that all rows that contain a value between 800 and 900 have
to be deleted
If Cells (i,3).Value>=800 AND Cells(i,3)<=900 Then EntireRow.delete
i=i+1
Loop

End sub

 

[JLatham]

You have i as an integer (a number), but your loop says to do until i = empty
string. That can't happen.
If you want the loop to run until you find an empty cell in column C, try it
this way

Do Until IsEmpty(Cells(i,3))

also, just for consistency, I'd change the IF statement to either
IF Cells(i,3).Value >=800 AND Cells(i,3).Value <=900 Then
Cells(i,3).EntireRow.Delete
End If

or
IF Cells(i,3) >=800 AND Cells(i,3)<=900 Then
Cells(i,3).EntireRow.Delete
End If

 

[JLatham]

Ruben,
One problem in deleting rows and using a counter/row pointer such as you
have done is that when a row is deleted, you end up skipping rows that may
have values in them that you want to delete. One easy way to do this is to
start at the bottom of the list and work your way up it instead of from the
top down.

Try this code
Sub DeleteSomeRows()
Dim lastRow As Long
Dim rowPointer As Long
'find last used row in column C
lastRow = Cells(Rows.Count,3).End(xlUp).Row
'work from bottom up
For rowPointer = lastRow To 1 Step -1
If Cells(rowPointer,3)=>800 And _
Cells(rowPointer,3)<=900 Then
Cells(rowPointer,3).EntireRow.Delete
Next
End Sub

 

[Don Guillett]

Assumes a header row

for i= cells(rows.count,3).end(xlup).row to 2 step -1
If Cells (i,3)>=800 AND Cells(i,3)<=900 Then rows(i).delete
next i

 

[Ruben]

It works, thanks for helping!

Ruben

Assumes a header row

for i= cells(rows.count,3).end(xlup).row to 2 step -1
If Cells (i,3)>=800 AND Cells(i,3)<=900 Then rows(i).delete
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)