MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Check content of cell and delete content if criteria isn't met

  Asked By: Meenachi    Date: Feb 02    Category: MS Office    Views: 1007

Had great luck with my first note, so without being too greedy, I'm
going to put a new challenge through ...

I'd like to be able to search a predefined set of columns for a name
and if the result isn't that name, then the content of the cells are
deleted (contents would consist of a formula that returns names). The
number of rows, however, will vary from month-to-month.

Target name: Sue <-- will put this in cell A3

Amy Sue (calc) (calc)
Sue (calc) (calc) (calc)
Jim Jerry Tim Sue

Can this even be done? How do I make the range dynamic (again, where
the number of rows will vary)?



3 Answers Found

Answer #1    Answered By: Eustatius Bakker     Answered On: Feb 02

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


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

Answer #2    Answered By: Ismet Yilmaz     Answered On: Feb 02

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.

Answer #3    Answered By: Malcolm Carter     Answered On: Feb 02

Try this:

Sub SeekAndDestroy()

Dim strCheck As String
Dim Flag As Boolean

strCheck = Range("A3").Value

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