CODEWITHSUNDEEP
vbaexcel
Rafael Osipov
Rafael Osipov

Reputation: 740

VBA If Not Or Statenent

I have If Not statement with 2 or but the code runs still like it is regular If statement. Moncol is an integer variable that equales 13 and the if statement should go to End If, and it is not. This code should delete columns just when Moncol not equals 12 or 13 or 14.

With NewPayWS
    If Not MonCol = 12 Or Not MonCol = 13 Or Not MonCol = 14 Then
        .Range(.Cells(1, lastcol - 1), .Cells(1, MonCol + 1)).EntireColumn.Delete
        .Range(.Cells(1, DataType + 1), .Cells(1, MonCol - 4)).EntireColumn.Delete
    End If
End With

Upvotes: 5

Views: 16418

Answers (3)

Shai Rado
Shai Rado

Reputation: 33692

Try Select Case instead, when having multiple scenarios of If and Else, it's much easier to use, and read.

Select Case MonCol
    Case 12, 13, 14
        ' do nothing

    Case Else
        .Range(.Cells(1, lastcol - 1), .Cells(1, MonCol + 1)).EntireColumn.Delete
        .Range(.Cells(1, DataType + 1), .Cells(1, MonCol - 4)).EntireColumn.Delete

End Select

Edit 1: Following @Rory comments, you can also use Case 12 To 14, this may come handy especially for ranges with a lot of values, then you can use Case 12 To 30, etc.

Upvotes: 6

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

There are multiple ways to handle it. Here's one more

If moncol >= 12 and moncol <=14 then
     'Do nothing
else
     'delete code
end if

Upvotes: 2

GTG
GTG

Reputation: 4954

Your current If statement will always result in True.

You can do:

With NewPayWS
    If Not (MonCol = 12 Or MonCol = 13 Or MonCol = 14) Then
        .Range(.Cells(1, lastcol - 1), .Cells(1, MonCol + 1)).EntireColumn.Delete
        .Range(.Cells(1, DataType + 1), .Cells(1, MonCol - 4)).EntireColumn.Delete
    End If
End With

Upvotes: 6

Related Questions