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

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

Yes, you are correct on your assumptions, except the delete should be 6
rows.

I couldn't get the above code to delete all the records with a "D" even
after modifying it to delete 6 rows.

I was hoping to merely modify my code. Is there a way to have it to
work from the bottom up?

In any event, I would like to keep it as simple as possible because I
am still learning.

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