Thank you for the tip on using the used range - I appreciate the help I
always get from this forum

"Vergel Adriano" wrote:

Christy,

Check the code below. Instead of using 3300 as the last row, you can
determine the last used row to make it more efficient. You also don't need
to select the cell to delete the row. But the most important thing is that
you need to go through the cells from bottom to top so you don't miss
anything.


Sub DeleteUnusedRows()
Dim i As Integer
Dim lLastRow As Long

lLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row

If lLastRow < 7 Then Exit Sub

For i = lLastRow To 7 Step -1
With Cells(i, 3)
If .Text < "Count required?" Then .EntireRow.Delete
End With
Next i
Range("a1").Select
End Sub


"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