I tried to fix your macro. But your macro appeared to:
1) Go through cells in Column(A).
2) When find a cell which is non-zero, then
3) If the cell 2 columns to the right contains a "D", delete the entire row.

The tasks looks strange to me... But, anyway, try the macro below:

Sub clean_rows()
Dim constantCells As Range
Dim tmp As Single
Dim tmp_address As String

tmp_address =
Worksheets("Roster").Range("A1").CurrentRegion.Col umns(1).Address(False,
False)

With Worksheets("Roster").Range(tmp_address)
If .Cells.Count = 1 And .Cells(1).Value = "" Then Exit Sub
For tmp = .Cells.Count To 1 Step -1
If .Cells(tmp).Value < 0 And .Cells(tmp).Offset(0, 2) = "D" Then
.Cells(tmp).EntireRow.Delete
End If
Next
End With
End Sub

Regards,
Edwin Tam

http://www.vonixx.com




"mikeburg" wrote:


I am having touble using looping to remove records that contain a D when
two are together using the following:

Dim constantCells As Range
Dim Cell As Range
Set constantCells =
Sheets("Roster").Range("A:A).SpecialCells(xlConsta nts)
For Each Cell In constantCells
If Cell.Value 0 Then
If Cell.Offset(0, 2) = "D" Then
Cell.Offset(0, 0).Resize(6).EntireRow.Delete
End If
End If
Next Cell

I know the problem is after the record is deleted the next cell is
skipped because the next command is Next Cell.

Can someone help me come up with the VBA code that deletes records when
the desired records are one after the other?

Thanks a million. mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=514655