Code problem

R

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
 
J

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
 
J

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
 
D

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
 
R

Ruben

It works, thanks for helping!

Ruben



Don Guillett said:
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
[email protected]
Click to expand...
 
Top