Delete only Columns containing "No"

[berty2000]

In VBA is it possible to delete the whole columns containing the word "No" in the range of C2:AM2 ?

Thanks in advance

 

[My Aswer Is This]

On My limited test my script worked but when all cells in row 2 had "No" it missed some. I figured working the range backwards would allow it to work. Live and learn. I do know about running scripts backwards in some cases.

That's why I was querying it... try the code below

Sub Delete_Columns()
Application.ScreenUpdating = False
Dim x As Long
    For x = 39 To 3 Step -1
        If Cells(2, x).Value = "No" Then Cells(2, x).EntireColumn.Delete
    Next
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Hi M.A.I.T. the trouble you were having is that when using the range method Excel always works from the lowest cell to the highest cell so when you wrote

Range("AM2:C2")

Excel (being helpful?) doesn't treat it as an error or work backwards, it interprets it as

Range("C2:AM2")

Then you get the usual Shift problem (similar to what you get when deleting rows).

See the order produced by the code below

Sub Demo()

Dim c As Range
    For Each c In Range("AM2:C2")
       Debug.Print c.Column
    Next

End Sub

 

[My Aswer Is This]

Yep. I know that now. Thanks.

Hi M.A.I.T. the trouble you were having is that when using the range method Excel always works from the lowest cell to the highest cell so when you wrote

Range("AM2:C2")

Excel (being helpful?) doesn't treat it as an error or work backwards, it interprets it as

Range("C2:AM2")

Then you get the usual Shift problem (similar to what you get when deleting rows).

See the order produced by the code below

Sub Demo()

Dim c As Range
    For Each c In Range("AM2:C2")
       Debug.Print c.Column
    Next

End Sub