CODEWITHSUNDEEP
vbaexcel
Asparagus
Asparagus

Reputation: 35

Delete Specific Columns based on Conditions

I'm currently trying to make a program that takes user input and stores the values in an array called FastenerNumbers. Based on these values the program then fills specific cells with a green color so that the user knows to enter values there. The thing is if a value in the array is 0 I would like to delete that column so that the worksheet is cleaner.

The issue I'm running into is that when a column is deleted during the for loop the cells shift left. Because of this some of the cells are essentially skipped over. To counteract this I've essentially had to brute force the program so that it loops several times to account for any skipped columns.

Here's the code:

'Make cells green for user to put inputs into
For i = 0 To UBound(FastenerNumbers)
    If FastenerNumbers(i) <> 0 Then
        With Range(Range("A14").Offset(0, 2 * i), Range("A14").Offset(FastenerNumbers(i) - 1, (2 * i) + 1))
            .Borders.LineStyle = xlContinuous
            .Interior.ColorIndex = 4
        End With
    End If
Next

'Define initial counter variable
j = 1

'    Do Until j = 5
'        For i = 0 To UBound(FastenerNumbers)
'            If FastenerNumbers(i) = 0 Then
'                Range(Range("A14").Offset(0, 2 * i), Range("A14").Offset(FastenerNumbers(i) - 1, (2 * i) + 1)).EntireColumn.Delete
'            End If
'        Next
'    Loop
'

Do
    For Each cell In Range("A14", Range("A14").Offset(, (UBound(FastenerNumbers) + 1) * 2))
        If cell.Interior.ColorIndex <> 4 Then
            cell.EntireColumn.Delete
        End If
        j = j + 1
        If j >= (5 * (UBound(FastenerNumbers) + 1) * 2) Then
            Exit Do
        End If
    Next
Loop

The pseudocode is another method I was going to use. I don't think either method is significantly better than the other. I would like the loops to be cleaner and more efficient though.

Upvotes: 2

Views: 98

Answers (2)

user3598756
user3598756

Reputation: 29421

it as simple as having a separate variable (j) counting the number of valid FastenerNumbers() valueslike follows

'Make cells green for user to put inputs into
For i = 0 To UBound(FastenerNumbers)
    If FastenerNumbers(i) <> 0 Then
        With Range(Range("A14").Offset(0, 2 * j), Range("A14").Offset(FastenerNumbers(i) - 1, (2 * j) + 1)) ' use j as the column relevant variable
            .Borders.LineStyle = xlContinuous
            .Interior.ColorIndex = 4
        End With
        j = j + 1 'update column relevant variable
    End If
Next

Upvotes: 1

TheGuyThatDoesn&#39;tKnowMuch
TheGuyThatDoesn&#39;tKnowMuch

Reputation: 1250

I haven't tested this so not entirely sure it works, but give this a shot. Essentially it keeps everything within the For loop, and if FastenerNumbers(i) = 0 then it deletes the column, reduces i by 1, then continues to the next (in that case the same number):

For i = 0 To UBound(FastenerNumbers)
    If FastenerNumbers(i) <> 0 Then
        With Range(Range("A14").Offset(0, 2 * i), Range("A14").Offset(FastenerNumbers(i) - 1, (2 * i) + 1))
            .Borders.LineStyle = xlContinuous
            .Interior.ColorIndex = 4
        End With
    Else
        Range(Range("A14").Offset(0, 2 * i), Range("A14").Offset(FastenerNumbers(i) - 1, (2 * i) + 1)).EntireColumn.Delete
        i = i - 1
    End If
Next

Upvotes: 1

Related Questions