Delete Column Loop VBA

Question

I loop through the Sheet via While, so when I find the string "Out" in the first row I delete the row. The problem is that when I delete that column the next one goes to the deleted position and thus the loop will pass and wont delete it.

Can you help me?

Thanks

Sub DeleteCol()

xx = 37
Do While Worksheets("Data").Cells(1, xx) <> ""

Agrup = Worksheets("Data").Cells(1, xx)
Rubri = Worksheets("Data").Cells(2, xx)
If Agrup = "Out" Then
    'Worksheets("Data").Columns(xx).Clear
    Worksheets("Data").Columns(xx).Delete Shift:=xlShiftToLeft
End If
xx = xx + 1
Loop

End Sub

Use a For Loop and loop backwards.

Scott Craner
– Scott Craner

2017-07-31 13:01:50 +00:00
Commented Jul 31, 2017 at 13:01 — Scott Craner
– Scott Craner, Commented Jul 31, 2017 at 13:01

Shai Rado · Accepted Answer · 2017-07-31 13:38:30Z

4

When deleting rows or columns you always need to loop backwards. Otherwise the current row/column position changes during delete.

This is because deleting always affects the position of rows/columns after the current row/column but not before. Therefore looping backwards does not affect our loop because un-processed rows/columns are before current row/column which are not affected by deleting.

Option Explicit 'very first line to ensure all variable are declared

Public Sub DeleteColumns()
    Dim ws As Worksheet
    Set ws = Worksheets("Data") 'define worksheet

    Dim Argup As Range, Rubri As Range
    Dim iCol As Long, lCol As Long 'iteration column, last column
    Const fCol = 37 'first column

    With ws '<-- use With statement
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column 'find last used column

        For iCol = lCol To fCol Step -1 'loop from last column backwards to column 37
            Agrup = .Cells(1, iCol)
            Rubri = .Cells(2, iCol)

            If Argup = "Out" Then
                .Columns(iCol).Delete Shift:=xlShiftToLeft
            End If
        Next iCol
    End With

End Sub

I also recommend to declare all variables and always use Option Explicit.

edited Jul 31, 2017 at 13:38

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

answered Jul 31, 2017 at 13:13

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vityata Over a year ago

This can end up easily in an endless loop, if the lCol is less than 37. Just noting.

Pᴇʜ Over a year ago

@Vityata if lcol is less than 37 then the loop is run 0 times.

Vityata Over a year ago

That's true. VBA is wiser than I thought.

Pᴇʜ Over a year ago

@Vityata "VBA is wiser than I thought" don't trust in that. It's a trap. Most times it is not.

Vityata Over a year ago

I usually assume the worst there. That's why I thought it would run endlessly.

Jakob Busk Sørensen · Accepted Answer · 2017-07-31 13:24:44Z

The answer is already posted, you need to delete from the end, rather than from the begining. But why? Lets image you have five columns, and you want to delete columns 2 and 4. Before deleting, this is how your columns look like:

Column index in loop:   | 1 | 2 | 3 | 4 | 5 |
Original column number: | 1 | 2 | 3 | 4 | 5 |

Now you start from the begining...

Index 1: Do nothing (as intended)
Index 2: Delete (as intended)

Let's pause for a minute. Now let us see how deleting column 2 changed the picture:

                        Loop direction ---->
Column index in loop:   | 1 | 2 | 3 | 4 | 5 |
Original column number: | 1 | 3 | 4 | 5 |

Now you might see that something is wrong. But lets continue the loop for the sake of the example:

Index 3: Do nothing (but this was originally column 4, and should have been deleted)
Index 4: Delete (but this was originally column 5, and should NOT have been deleted)
Index 5: Do nothing (depending on your code, you might get and out of bounds error)

Lets try to go backwards instead, maybe that helps:

Index 5: Do nothing (as intended)
Index 4: Delete

Let us pause, once again, to see how it looks now

                        <---- Loop direction
Column index in loop:   | 1 | 2 | 3 | 4 | 5 |
Original column number: | 1 | 2 | 3 | 5 |

Well, something did shift. But as the next step in the loop is 3 (and we are going backwards), it doesn't really matter. Hope that helps the understanding a bit :-)

Collectives™ on Stack Overflow

Delete Column Loop VBA

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related