Using AND to eliminate IF statements

[Ken Loomis]

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

 

[Guest]

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

 

[sulprobil]

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

 

[Philo Hippo]

For Row = totalrows To 6 Step -1
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 And _
Cells(Row, ServLevelCol).Value <> Empty Then
Cells(Row - 1, ServLevel).Value = _
Cells(Row - 1, ServLevel).Value & _
"/" & Cells(Row, ServLevel).Value

Rows(Row).Delete
count = count + 1
End If
Next