Solved VBA - Test cell for multiple values

[Peatawn]

Hi,

I built a large macro a while back that I need to reduce because with the recent additions I have made, I got "Procedure too large" as an error upon execution. I've already condensed most of my coding to something more elegant (I keep learning new things every time). Now I'd like to reduce the following code :

Do Until ActiveCell.Column = LastCol + 1
If ActiveCell.Value = "resRespondent" Or ActiveCell.Value = "ResLastCallDate" Or ActiveCell.Value = "LastCallStartTime" Or ActiveCell.Value = "resCallCount" _ ''And it goes on like this for many more values
Then
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.EntireColumn.Delete
LastCol = LastCol - 1
End If
Loop

This is actually a big chunk of code and I have many more of these chunks in my macro. The problem is always having to repeat "Or ActiveCell.Value = " before each test. Something along the following idea would be great:

If ActiveCell.Value = "this", "that", "or this", "or that" Then

Thanks,
Peatawn

 

[Alejandro85]

You can use SELECT CASE instead of IF, which allows such multiple values when comparing the same variable to many constants:

SELECT CASE ActiveCell.Value
    CASE "resRespondent", "ResLastCallDate", "LastCallStartTime", "resCallCount"
        ActiveCell.Offset(0, 1).Select
    CASE ELSE
        ActiveCell.EntireColumn.Delete
        LastCol = LastCol - 1
END SELECT

Which is functionally equivalent to your example. Try it, as I've not actually tested it.

 

[Peatawn]

Thanks Alejandro! This works perfectly. Not only did you help me reduce my code, but you also taught me something new.