CODEWITHSUNDEEP
vbaexcel
Alec Terry
Alec Terry

Reputation: 37

exiting for loop too quickly vba

I am brand new to programming and currently working on trying to learn vba. I'm having an issue with a for each loop I am trying to run where I exit my loop too quickly. I have a range that I'd like to test to see if they have certain values present and if so, I'd like to delete the column. But it only tests the condition one time and then exits the loop. Would definitely appreciate any help on this!

  For Each cell In ActiveSheet.Range("S1:AA1")


    If ActiveCell.Value = "Actual" Then
        ActiveCell.EntireColumn.Delete


    ElseIf ActiveCell.Value = "" Then
        ActiveCell.EntireColumn.Delete


    Else: ActiveCell.Offset(, 1).Select


    End If

Exit For

Next

Upvotes: 1

Views: 110

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Use cell instead of ActiveCell, and there is no need to Select.

Also, your Exit For statement seems suspect, this will exit on the first cell. You generally only use an Exit statement when some condition has been met which requires exiting the loop before processing all of the items (i.e., a loop designed to find the first instance of something, etc.).

Finally, when deleting members from a collection, you need to do it in reverse order (otherwise, extraordinary measures must be taken to ensure all items are processed).

Dim i as Long
Dim cell as Range
For i = ActiveSheet.Range("S1:AA1").Columns.Count to 1 Step - 1
    Set cell = ActiveSheet.Range("S1:AA1").Cells(i)
    Select Case cell.Value
        Case "Actual", ""
           cell.EntireColumn.Delete
        Case Else
           'Do nothing, unless you need to do something...
    End Select
Next

NB: Case-sensitivity and trailing/leading spaces. If you're concerned about "ACTUAL", "actual", "AcTuAl", etc., and values like " " and "Actual ", you should do:

Select Case UCase(Trim(cell.Value))
    Case "ACTUAL", ""
...

Upvotes: 5

Related Questions