I need someone to take a look at this code for me and telling me what I am
doing wrong.
What I am trying to do is delete any row where this condition is true:
c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed" And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
In other words, if the values in these cells are anything but complete,
delete the entire row.

For Each c In Worksheets("Ready for DDS").Range("B2:B" & SL_lRow).Cells
If c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed"
And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete

Ayo,

The code works perfectly provided the cells do contain the text Completed.

if it isn't working for you; and clearly it isn't, then a couple of things
to consider.

1. The string comparison is case sensitive.
2, Do you really mean that ALL of these cells must not contain Completed
because that's what the code is saying i.e to be deleted ALL 4 cells must not
contain the text string.
3. have you dimmed rngAll as a range

Lastly tell us what result you getting.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Ayo" wrote:

I need someone to take a look at this code for me and telling me what I am
doing wrong.
What I am trying to do is delete any row where this condition is true:
c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed" And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
In other words, if the values in these cells are anything but complete,
delete the entire row.

For Each c In Worksheets("Ready for DDS").Range("B2:B" & SL_lRow).Cells
If c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed"
And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete

This is the entire sub:

Sub buildDDS_SITELIST()
Dim SL_lRow As Long, c As Range, rngAll As Range
SL_lRow = Worksheets("Sites Task List").Cells(Rows.Count,
"A").End(xlUp).Row

Worksheets("Sites Task List").Range("A2:J" & SL_lRow).Copy
Worksheets("Ready for DDS").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("B2").Select
Worksheets("Sites Task List").Range("S2:S" & SL_lRow).Copy
Worksheets("Ready for DDS").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

For Each c In Worksheets("Ready for DDS").Range("B2:B" & SL_lRow).Cells
If c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed"
And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete
End Sub

I am getting cells with Open, InProgress and Not Required in the cells,
while I only want cells with Completed.

"Mike H" wrote:

Ayo,

The code works perfectly provided the cells do contain the text Completed.

if it isn't working for you; and clearly it isn't, then a couple of things
to consider.

1. The string comparison is case sensitive.
2, Do you really mean that ALL of these cells must not contain Completed
because that's what the code is saying i.e to be deleted ALL 4 cells must not
contain the text string.
3. have you dimmed rngAll as a range

Lastly tell us what result you getting.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Ayo" wrote:

I need someone to take a look at this code for me and telling me what I am
doing wrong.
What I am trying to do is delete any row where this condition is true:
c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed" And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
In other words, if the values in these cells are anything but complete,
delete the entire row.

For Each c In Worksheets("Ready for DDS").Range("B2:B" & SL_lRow).Cells
If c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed"
And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete

I mean, if anyone of these cells contain anything other than "Completed",
delete the entire row. But if all the cells contains "Completed" keep the row.

"Mike H" wrote:

Ayo,

The code works perfectly provided the cells do contain the text Completed.

if it isn't working for you; and clearly it isn't, then a couple of things
to consider.

1. The string comparison is case sensitive.
2, Do you really mean that ALL of these cells must not contain Completed
because that's what the code is saying i.e to be deleted ALL 4 cells must not
contain the text string.
3. have you dimmed rngAll as a range

Lastly tell us what result you getting.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Ayo" wrote:

I need someone to take a look at this code for me and telling me what I am
doing wrong.
What I am trying to do is delete any row where this condition is true:
c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed" And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
In other words, if the values in these cells are anything but complete,
delete the entire row.

For Each c In Worksheets("Ready for DDS").Range("B2:B" & SL_lRow).Cells
If c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed"
And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete

I got it!!!. All I need was to change:
c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed" And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
to
c.Offset(0, 2) < "Completed" Or c.Offset(0, 3) < "Completed" Or _
c.Offset(0, 4) < "Completed" Or c.Offset(0, 5) < "Completed"
Thanks for all the help.

"Mike H" wrote:

Ayo,

The code works perfectly provided the cells do contain the text Completed.

if it isn't working for you; and clearly it isn't, then a couple of things
to consider.

1. The string comparison is case sensitive.
2, Do you really mean that ALL of these cells must not contain Completed
because that's what the code is saying i.e to be deleted ALL 4 cells must not
contain the text string.
3. have you dimmed rngAll as a range

Lastly tell us what result you getting.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Ayo" wrote:

I need someone to take a look at this code for me and telling me what I am
doing wrong.
What I am trying to do is delete any row where this condition is true:
c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed" And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
In other words, if the values in these cells are anything but complete,
delete the entire row.

For Each c In Worksheets("Ready for DDS").Range("B2:B" & SL_lRow).Cells
If c.Offset(0, 2) < "Completed" And c.Offset(0, 3) < "Completed"
And _
c.Offset(0, 4) < "Completed" And c.Offset(0, 5) < "Completed"
Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete