find text then delete columns

[gaz8000]

I have a large excel sheet and that has multiple columns that contains the word frozen in row a1. I have to manually find these and then delete the entire column. The sheet could contain upto 50 occurances.

Could this find of frozen and then the column delete be done by VBA code?

TIA

 

[VoG]

Try

Sub Frozen()
Dim LC As Long, j As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = LC To 1 Step -1
    If Cells(1, j).Value Like "*frozen*" Then Columns(j).Delete
Next j
End Sub

 

[gaz8000]

That worked a treat
Thanks very much.

Is there also a way to hilite cells in a color if the column hdr = completed
and the cell under it = y. there could be lots of these?

 

[VoG]

Try

Sub Frozen()
Dim LC As Long, j As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = LC To 1 Step -1
    If Cells(1, j).Value Like "*frozen*" Then
        Columns(j).Delete
    ElseIf Cells(1, j).Value Like "*completed*" And Cells(2, j).Value = "y" Then
        Cells(1, j).Interior.ColorIndex = 3
    End If
Next j
End Sub

 

[gaz8000]

Thanks again Vog.

This time doesn't quite work

It changes the colors for the cells in the first column of complete, but there are multiple columns with complete in the hdr and these are just ignored

 

[VoG]

My results

Excel Workbook
	A	B	C	D	E
1	a	b	completed	completed	completed
2			y	n	y
Sheet2

 

[gaz8000]

Sorry, I didn't explain it too well

Its the "Y' or 'P' s that need to be hi-lited if the column hdr contains completed

 

[VoG]

Try this

Sub Frozen()
Dim LC As Long, j As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = LC To 1 Step -1
    If Cells(1, j).Value Like "*frozen*" Then
        Columns(j).Delete
    ElseIf Cells(1, j).Value Like "*completed*" And (Cells(2, j).Value = "y" Or Cells(2, j).Value = "p") Then
        Cells(2, j).Interior.ColorIndex = 3
    End If
Next j
End Sub

 

[gaz8000]

Thanks again, still not working though

this is a screenshot of the results
http://cl.ly/0e0q3s063j0t182C1h0x

As you can see the first two occurrences of Y have been changed on the first row!
but all the Y's in the rows under the completed cell should be hi-lited.

also the columns to the right that contain completed (col M + P) seem to be ignored (there are more occurrances to the right as well)

hope thats a bit clearer

 

[VoG]

Perhaps this

Sub Frozen()
Dim LC As Long, j As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = LC To 1 Step -1
    If Cells(1, j).Value Like "*frozen*" Then
        Columns(j).Delete
    ElseIf LCase(Cells(1, j).Value) Like "*complet*" And (LCase(Cells(2, j).Value) = "y" Or LCase(Cells(2, j).Value) = "p") Then
        Cells(2, j).Interior.ColorIndex = 3
    End If
Next j
End Sub