delete a cell if it contains specific word

[Guest]

Im trying to write a formula were i can get the programe to check the file
and if a cell contains 'agency' i want it to delete the cell and shift it
left. the reason for the formula is that the file will be in a differnt
order/size each time, and cannot just do this manually.

Thanks

 

[Guest]

Enter and run this macro:

Sub Macro1()
Dim i, j As Long
Dim s, ss As String
ss = "agency"
For i = 10 To 1 Step -1
For j = 10 To 1 Step -1
s = Cells(j, i).Value
If Not (InStr(s, ss) = 0) Then
Cells(j, i).Delete Shift:=xlToLeft
End If
Next
Next
End Sub


As coded it covers 10 rows by 10 columns. Expand it to suit your needs.