Clearing cells based upon criteria?

[shaunap]

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

 

[Dave Peterson]

You could use a worksheet_change event that would look at that key cell and
process the clearing when you make a change to that cell.

But since you have a macro already available, you could add a button from the
Forms toolbar and assign the same macro to that button.

Then just click the button (instead of ctrl-c) to run the macro to clear the
range.

 

[KC Rippstein hotmail com>]

Use a Worksheet_Change macro.

Right-click on the sheet tab and select "View Code."
In the drop-down at the top of the right-side work area, where it says
"(General)" select the drop-down list entry called "Worksheet"
Then in the drop-down to the right of that select the entry called "Change"
Now just write your macro. Here is an example which clears the range C3:C4
if A1's value is 2008:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = "2008" Then
Range("C3:C4").Value = ""
End If
End Sub

 

[Gord Dibben]

First of all, select the cells to clear and Insert>Name>Define

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

 

[Shane Devenshire]

Hi,

You might use a formula, but then the cell will display as blank but it will
actually have a formula in it.

For example =IF(A1=10,"",A1)

This formula displays as blank when A1=10 otherwise it displays the value in
A1.

If this is not good enough, you can add a Worksheet_Change event macro

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
'Your code here
End If
End Sub

This code goes into the Sheet1 object. (or the equivalent object in your
file.) The range A1 is the cell whose change triggers the macro.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[shaunap]

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!

 

[shaunap]

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!