entirecolumn delete is throwing off my for each loop

[bfreescott]

This snippet of my code is behaving oddly (at least to me) because when the column of range c is deleted, c then becomes c plus one, instead of next c.

I hope that is clear enough, but just in case it's not, let me give an example:

When looping through a row of ranges on row 11, if c is in column D and I delete column D, c becomes column F, when I want it to be E.
I don't understand the behavior, but I'm wondering if I should just avoid it altogether and add all the ranges where intVariable = 2 into an array and wait until after the For Next loop to finish before deleting the columns in the array. Recommendations?

(Caveat: I'm not a fan of arrays, so if you think that's the best practice here, can you provide an example?)

            For Each c In .Range("E11:M11")
                If c.Offset(11, 0).Value = 0 Then
                    If intVariable = 2 Then
                        c.EntireColumn.Delete
                    ElseIf intVariable = 1 Then
                        c.EntireColumn.Hidden = True
                    End If
                End If
            Next c

 

[Jonmo1]

When Deleting columns (or rows) you have to go backwards (from column M to E)
It's a little easier with rows, but doable with columns..

Try

For i = 13 To 5 Step -1
    If .Cells(11, i).Offset(11, 0).Value = 0 Then
        If intVariable = 2 Then
            .Columns(i).EntireColumn.Delete
        ElseIf intVariable = 1 Then
            .Columns(i).EntireColumn.Hidden = True
        End If
    End If
Next i

If figuring the column #s is impracticle, you can do

For i = Cells(1, "M").Column To Cells(1, "E").Column Step -1

 

[Norie]

When deleting loop backwards, in this case from M to E.

For I =13 To 5 Step -1

    Set c = .Cells(11, I)

    If c.Offset(11, 0).Value = 0 Then
        If intVariable = 2 Then
            c.EntireColumn.Delete
        ElseIf intVariable = 1 Then
            c.EntireColumn.Hidden = True
       End If
   End If

Next I

 

[bfreescott]

Thanks guys..

That actually increased the efficiency of my code as well since my right-most column changes on each sheet and I was using split and address to get the column letter for my for next loop. I can skip that now.

 

[Jonmo1]

You're welcome.