Here is another possibility

Sub DeleteUnusedrows()
columns(3).specialCells(xlconstants,xlNumbers).Ent irerow.delete
end Sub

or if you want to protect cells above row 7

Sub DeleteRows()
dim rng as Range
set rng = Range(cells(7,3),cells(rows.count,3).End(xlup))
rng.specialCells(xlConstants,xlNumbers).Entirerow. Delete
End Sub

these assume all entries in column C that need to be deleted will have a
hard coded number in them.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Sub DeleteUnusedRows()
Dim i As Long
For i = 3300 To 7 Step -1
If lcase(Cells(i, 3).Value) < "count required?" Then
rows(i).Delete
End If
Next i
Range("a1").Select
End Sub

--
Regards,
Tom Ogilvy


"Christy" wrote:

I have the following data and I am trying to make a loop to delete all row
except the ones that have the words 'Count Required' in column C

A B C D
UNIT NAME AUDIT WITHIN?
123 Main 0.19 YES
123 Main 0.43 YES
123 Main -13.44 NO
123 Main Count Required? YES
456 Smallville -25.77 NO
456 Smallville 95.76 NO
456 Smallville 23.42 YES
456 Smallville Count Required? YES
789 Anytown 8.04 YES

I tried the following loop which only deletes some of the rows. I have to
run it seveal times to get what I want. Can anyone tell what I am doing wrong
or offer a better way to accomplish my task?

Sub DeleteUnusedRows()
Dim i As Integer
For i = 7 To 3300 Step 1
Cells(i, 3).Select
If ActiveCell.Value < "Count required?" Then
ActiveCell.EntireRow.Delete
End If
Next i
Range("a1").Select
End Sub