Well, a starting point might be the CurrentRegion property of the
selection object. You could do it like this:

Range("AA1").CurrentRegion.Select

Try it and see the result  you get. It's pretty good. You might need to
restructure your sheet a bit to sort of put  a moat around the data. Try
it out and you'll see what I mean.

Once that's selected, it's pretty straightforward to look at every cell
in the selecion in turn and evaluate it's value. It would be something
like this:

strCheck = Range("A3").value

For each c in Selection
If c.Value <> strCheck Then c.Formula=""
Next c

My apologies for not being clear. I only want to wipe
out the cells  to the right of the target  (indicated in
cell A3) in the current region.

Try this:

------------------------------------
Sub SeekAndDestroy()

Dim strCheck As String
Dim Flag As Boolean

strCheck = Range("A3").Value
Range("AA1").CurrentRegion.Select

For Each r In Selection.Rows
Flag = False
For Each c In r.Columns
If Flag = True Then c.Formula = ""
If c.Value = strCheck Then Flag = True
Next c
Next r

End Sub