CODEWITHSUNDEEP
vbaexcelfor-loop
Darren Gillen
Darren Gillen

Reputation: 47

VBA 'For' function not completing cycle

I have a series of columns in a sheet called "Optimisation". Some of the columns contain just one entry ("FAIL") and others contain a set of integers.

I need to cycle through the columns and delete the ones that have FAIL in them. The code I have used is as follows:

Sub delete_fails()

Dim empty_column1 As Integer
Dim iTimes As Long
Dim iRepeat As Integer

iTimes = Application.WorksheetFunction.CountA(Sheets("Optimisation").Range("1:1"))

empty_column1 = 1

    For iRepeat = 1 To iTimes
        If Sheets("Optimisation").Cells(1, empty_column1).Value = "FAIL" Then
            Sheets("Optimisation").Columns(empty_column1).EntireColumn.Delete
        End If
        empty_column1 = empty_column1 + 1
        Next iRepeat

End Sub

The code partially works because it deletes some of the FAIL columns but doesn't delete them all. I have to run the macro 10-15 times before all the FAIL columns are successfully deleted. Is there a bug in the code that I can clear up so I only have to run the macro once? Maybe I need to instruct Excel to ignore any column with numbers in it and move on to the next one?

(Note: Total VBA newb here so apologies in advance if the coding hurts your brain.)

Upvotes: 1

Views: 111

Answers (2)

Shai Rado
Shai Rado

Reputation: 33662

When deleting objects, in your case Columns, allways loop backwards, so change your loop:

For iRepeat = 1 To iTimes

To:

For iRepeat = iTimes To 1 Step -1

Try the code below: 

With Sheets("Optimisation")
    For iRepeat = iTimes To 1 Step -1
        If .Cells(1, iRepeat).Value = "FAIL" Then
            .Columns(iRepeat).Delete
        End If
    Next iRepeat
End With

Upvotes: 3

SBF
SBF

Reputation: 1369

Don't increment empty_column1 when you have deleted a column.

For iRepeat = 1 To iTimes
    If Sheets("Optimisation").Cells(1, empty_column1).Value = "FAIL" Then
        Sheets("Optimisation").Columns(empty_column1).EntireColumn.Delete
    Else
        empty_column1 = empty_column1 + 1
    End If
Next iRepeat

Upvotes: 0

Related Questions