Problem with "Or" Function in VBA?

Question

I have the following code:

Sub KeepOnlyUnitAllocation()

Dim RowToTest As Long

Sheets("Sheet..").Select

Deletes all row less than specified value up to row 2

(excludes row 1)

For RowToTest = Cells(Rows.Count, 9).End(xlUp).Row To 2 Step -1

With Cells(RowToTest, 9)

If (.Value <> "AMC Charge" Or .Value <> "Unit Allocation") Then

Rows(RowToTest).EntireRow.Delete

End If

End With

Next RowToTest

End Sub

Update:

If I try the code with only one or the other, it works fine, but with both it just deletes all lines.

VP · Accepted Answer

I think BT_Bot has it right. Those "not equal" conditions are throwing things off. I'd also add the extra parens, but I think AND should be used as well.

Intent: the line cannot have "AMC Charge" nor can it have "Unit Allocation".

If David had used the positive version of this then he could have used OR since only 1 test needs to be True: If ((.value == "AMC Charge") OR (.value == "Unit Allocation")) {leave the line unchanged}

But since he used the negative version he needs BOTH tests to be True -- which calls for an AND:

... If ((.value <> "AMC Charge") AND (.value <> "Unit Allocation")) {delete the line}

BT_Bot · Answer

Try AND instead of OR.

Condition 1 is <>"AMC Charge"

Condition 2 is <>"Unit Allocation"

OR between condition 1 and 2 will ALWAYS be true (true/false or false/true or true/true).

Using AND both conditions need to be satisfied ie value needs to be neither AMC Charge nor Unit Allocation to delete the line.

Anonymous · Answer

Or has a higher precedence than <> If you want to override that order, use parentheses: If ((.Value <> "AMC Charge") Or (.Value <> "Unit Allocation")) Then Rows(RowToTest).EntireRow.Delete

Crim Liar · Answer

A long time since I've played hard with VBA but I would add extra parenthasis: If ((.Value <> "AMC Charge") Or (.Value <> "Unit Allocation")) Then

Trending News

Problem with "Or" Function in VBA?

4 Answers