Solved: Search row for text and delete column if equal to a value

[AleemAM123]

Hi Everyone,

I'm new to this forum and to using VB (no formal training in VB just picked up pieces by experimenting), I usually just record macros rather than write them but I need to do something that requires some programming.

I need to search a row for the letters TI or TRC or FRC (they are part of a word though e.g. 50-TRC-015) and if the letters are not present then I want to delete the entire column.

Saw this code on the site and tried modifying it but I didn't have much success:

[VBA]
Sub LeaveOnlyAEFPOriginal()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
test = Cells(x, col).Text Like "[AEFPaefp]"
If test = False Then Cells(x, col).EntireRow.Delete
Next
End Sub
[/VBA]
Any help would be greatly appreciated, thanks.

[Bob Phillips]

Your requirements seem at odds to me. You say you want to search a row for the text but delete the column? Is that correct, or do you mean thge row? process all rows or just one particular row? Will the text be in one specific column, or possibly in (m)any?

[AleemAM123]

process one row and delete any column in which the text is not found for the row that I'm searching in. So in the attached file, I would want to delete column C,D,G,H and I.

[Bob Phillips]

Select row 4 and run this

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastCol As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastCol = .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft).Column
For i = LastCol To 1 Step -1

If Not .Cells(ActiveCell.Row, i).Value Like "*TI*" And _
Not .Cells(ActiveCell.Row, i).Value Like "*TRC*" And _
Not .Cells(ActiveCell.Row, i).Value Like "*FRC*" Then

.Columns(i).Delete
End If
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[AleemAM123]

that's just great, works like a charm. thanks xld.

[mdmackillop]

Hi Aleem
If this is solved, please mark it so using the Thread Tools dropdown.