Using AND to eliminate IF statements
 

When the final data is all assembled, there are duplicates that need to be
eliminated, but entries in a certain column from the duplicates need to be
combined and left in that column for the final unique entry.

I use this to do that:

For Row = totalrows To 6 Step -1
If Cells(Row, nameCol).Value = Cells(Row - 1, nameCol).Value Then
If Cells(Row, streetNumCol).Value = Cells(Row - 1,
streetNumCol).Value Then
If Cells(Row, StreetNameCol).Value = Cells(Row - 1,
StreetNameCol).Value Then
If Cells(Row, unitNumCol).Value = Cells(Row - 1,
unitNumCol).Value Then
If Cells(Row, ServLevelCol).Value < Empty Then
Cells(Row - 1, ServLevel).Value = _
Cells(Row - 1, ServLevel).Value & _
"/" & Cells(Row, ServLevel).Value
End If
Rows(Row).Delete
count = count + 1
End If
End If
End If
End If
Next Row

With over 8000 rows, this takes a little time and I was wondering if an AND
construct would be faster.

I just don't know the syntax to combined those first 4 IF statements using
AND.

Was also wondering if there weren't a more elegant way to do this.

Thanks for any suggestions.

Ken Loomis

Using AND to eliminate IF statements
 

If Cells(Row, nameCol).Value = Cells(Row - 1, nameCol).Value AND _
Cells(Row, streetNumCol).Value = Cells(Row - 1,streetNumCol).Value AND _
Cells(Row, StreetNameCol).Value = Cells(Row - 1, StreetNameCol).Value
AND _
Cells(Row, unitNumCol).Value = Cells(Row - 1, unitNumCol).Value then

'do your stuff here
End if

only other way i can think is to put all the columns in a array and loop
through them and
if any of the condition returns false, set a variable to false and break the
loop.
check after the loop if the variable is true then do next statement else not


"Ken Loomis" wrote:

When the final data is all assembled, there are duplicates that need to be
eliminated, but entries in a certain column from the duplicates need to be
combined and left in that column for the final unique entry.

I use this to do that:

For Row = totalrows To 6 Step -1
If Cells(Row, nameCol).Value = Cells(Row - 1, nameCol).Value Then
If Cells(Row, streetNumCol).Value = Cells(Row - 1,
streetNumCol).Value Then
If Cells(Row, StreetNameCol).Value = Cells(Row - 1,
StreetNameCol).Value Then
If Cells(Row, unitNumCol).Value = Cells(Row - 1,
unitNumCol).Value Then
If Cells(Row, ServLevelCol).Value < Empty Then
Cells(Row - 1, ServLevel).Value = _
Cells(Row - 1, ServLevel).Value & _
"/" & Cells(Row, ServLevel).Value
End If
Rows(Row).Delete
count = count + 1
End If
End If
End If
End If
Next Row

With over 8000 rows, this takes a little time and I was wondering if an AND
construct would be faster.

I just don't know the syntax to combined those first 4 IF statements using
AND.

Was also wondering if there weren't a more elegant way to do this.

Thanks for any suggestions.

Ken Loomis

Using AND to eliminate IF statements
 

The AND's are slower, aren't they?

I suggest to stick to your nested IF-statements but to
order them: Take that IF-statement first which is most
likely to return FALSE.

HTH,
sulprobil