entirecolumn delete is throwing off my for each loop

bfreescott

Board Regular
Joined
Aug 6, 2015
Messages
115
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?)

Code:
            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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
Code:
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
Code:
For i = Cells(1, "M").Column To Cells(1, "E").Column Step -1
 
Upvote 0
When deleting loop backwards, in this case from M to E.
Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top