help with loop to delete rows

[Christy]

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

 

[Vergel Adriano]

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(xlCellTypeLastCell).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

 

[Tom Ogilvy]

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

 

[Don Guillett]

The trick is to work from the bottom up. Also NO selections. Try this OR
sort and delete.
Sub DeleteUnusedRows()
Dim i As long
for i=cells(rows.count,3).end(xlup).row to 7 step -1
if ucase(cells(i,3))<>"COUNT REQUIRED?" Then rows(i).delete
Next i
End Sub

 

[Jason Lepack]

Do you really need to delete them or could you just filter the list?

The problem with your macro is this:
column A
1
2
3
4

for i = 1 to 3
debug.print cells(i,3)
next i
will print:
1
2
3

but
for i = 1 to 3
if cells(i,3) = 2 then
cells(i,3).entirerow.delete
end if
debug.print cells(i,3)
next i

will print:
1
3
4

So if you want to actually check each row then you need to step back
one after you perform your delete. Thus the i = i - 1 that I've
added.

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
i = i - 1
End If
Next i
Range("a1").Select
End Sub

 

[Tom Ogilvy]

Here is another possibility

Sub DeleteUnusedrows()
columns(3).specialCells(xlconstants,xlNumbers).Entirerow.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.

 

[Christy]

Perfect -Thanks so much!

Here is another possibility

Sub DeleteUnusedrows()
columns(3).specialCells(xlconstants,xlNumbers).Entirerow.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.

 

[Christy]

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