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

 

[berty2000]

I can delete rows with "No" in but im after deleting columns instead, this is what I have for rows but cant convert it to work with columns, any help guys.

Sub test()
With ActiveSheet
    .AutoFilterMode = False
    With Range("C2", Range("C" & rows.Count).End(xlUp))
        .AutoFilter 1, "*No*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

 

[My Aswer Is This]

Try this:

Sub Delete_Columns()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In Range("AM2:C2")
        If c.Value = "No" Then c.Columns.Delete
    Next
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Hi M.A.I.T do you get any issues if you run your code with the set up below?

Excel 2010
	C	D	E	F	G	H	I
1
2	No	No	No	No	No	No	No
Sheet1

 

[berty2000]

Hi My answer Is This,

The code is nearly there it deletes the cells with "No" in but not the entire column

 

[My Aswer Is This]

Sorry missed that.
Try this:

Sub Delete_Columns()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In Range("AM2:C2")
        If c.Value = "No" Then c.EntireColumn.Delete
    Next
Application.ScreenUpdating = True
End Sub

 

[MARK858]

I still think there is an issue with the layout in post # 4.

 

[berty2000]

That works perfect now, but for some reason i need to run the code twice to get all of the columns, I have never seen that before, any ideas?

 

[MARK858]

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

 

[berty2000]

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 I see what you mean now, your code does it perfect. Thank you so much for preserving with this .
Top banana mate.