Thank you all for your responses. I'm probably being thick headed here but
I'm having trouble wrapping my brain around the Worksheet Change function. I
did play with it a bit before posting but don't understand it enough.

Cell I4 always has a string in it. If no string it buggers up the query and
resulting calculations. "TheRange" and cell I4 are user input. I4 is the
main input. "TheRange" is additional query pulls. Not every query pull
requires additional pulls therefore to eliminate user forgetfullness it will
be better to clear "TheRange" everytime the main pull (I4) is changed. I4 is
possible to be different everytime the sheet is reopened and will never be
the same string, therefore I need the Worksheet Change to clear "TheRange"
regardless of what was changed in I4 simply that there was a change to the
string.

Hopefully this makes more sense to all who are/can help.

Thank you!

"Gord Dibben" wrote:

First of all, select the cells to clear and InsertNameDefine

Give the range a name of TheRange.

Add this event code to the sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$2" And Target.Value < "" Then 'adjust the $A$2
Me.Range("TheRange").ClearContents
End If
stoppit:
Application.EnableEvents = True
End Sub

Question: How do the cleared cells get re-populated?

Otherwise not much point in doing it automatically each time A2 gets
changed.


Gord Dibben MS Excel MVP

On Tue, 30 Dec 2008 08:45:00 -0800, shaunap
wrote:

I have a worksheet that I would like to have specific cells cleared to
nothing if the value in one specific cell changes. So if I change the one
cell at any time and point then the other specific cells clear out of
anything. Can anyone tell me if this is possible and how I would go about
it. Currently I have a macro set up that clears the cells if you "CTRL + c"
but I would like it to do it automatically.

Thanks for your time.
Shauna