Delete rows with macro code

[GregA]

Hi,

I would like to try and find a macro code that does the following:

1. Delete rows when cells in column C3:C2000 are less than (<) 50, and;

2. Delete rows when cells in column F3:F2000 are exactly equal to (=)
"0"

This 'exactly' is important as I have some blank cells in column F
which are greater than 50 and I need them to remain within the sheet.

If anyone can give me any assistance on this, I would be grateful.

Many Thanks,

Greg

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "C").Value < 50 Or _
(Len(Cells(i, "F").Value) > 0 And Cells(i, "F").Value = 0) Then
Rows(i).Delete
End If
Next i

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Don Guillett]

something like?

for i=2000 to 3 step -1
if cells(i,"c")<50 or cells(i,"f")=0 then rows(i).delete
next i

 

[OM]

You CAN write code if you want (as others have suggested), but what I do
when I want to do this (and sorry to the purists....) is insert a formula in
a blank column (insert a column if you have to) - something like =if
(or(c3=0,c3<50)=True,1,2) Then sort on the new column, delete the rows with
1 in them (which should be all together if you have sorted, and can be
deleted easily as a block). If you want to get fancy, put a second column in
with a reference number, so that you can get back to your original order for
the remaining rows. Then delete these extra columns. Job Done.

Rob