Search for a cell.

[Tree*Rat]

I need to search for data in unknown cell ranging from
for example C12 to J12 (there may be more coulmns so i need to check the
cell is not empty, if empty then no need to check next column as it the
end of the list). Then I need to know which column the data is in
to delete the column.

Someone has already sujected recording a macro but i have had no luck in
this.

Bug thanks

 

[Bernie Deitrick]

Tree*Rat,

Sub WhichColumn()
Dim myC As Range
Dim myS As String

myS = "What to Find"
Set myC = ActiveCell.EntireRow.Find(myS, , xlValues)
If myC Is Nothing Then
MsgBox myS & " was not found"
Else
MsgBox myS & " was found in column " & myC.Column
myC.EntireColumn.Delete
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[Roger Govier]

Hi

This should do what you want

Sub findDelete()
Dim c As String
Dim Rng As Range
c = InputBox("Enter string to delete.")
Set Rng = Nothing
Set Rng = Range("C12:J12").Find(what:=c, _
After:=Range("C12"), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
Rng.EntireColumn.Delete shift:=xlLeft
End Sub