Clearcontents
 

Vinnie

You can loop through all the dependent cells and clear their contents.
Here's an example that uses the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

'Stop this sub from running based on changes made here
Application.EnableEvents = False

Dim cell As Range
Dim DepRng As Range

'Changed cell is in A1:I1
If Not Intersect(Target, Me.Range("A1:I1")) Is Nothing Then

'Change cell was deleted
If IsEmpty(Target) Then

'Set up error handling in case their is no dependents
On Error Resume Next

Set DepRng = Target.Dependents

'If there are dependent cells
If Err.Number = 0 Then

'Loop through them and clear them
For Each cell In DepRng.Cells
cell.ClearContents
Next cell
End If
On Error GoTo 0
End If
End If

Application.EnableEvents = True

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Vinnie" wrote in message
...
How do I clear the contents from a range of cells, which are dependent on
another cell being populated. For example, If I enter a Value in cell A1,
A5:A10 are populated with data according to the value entered in A1. A value
in B1 populates data in B5:B10, C1 populates data in C5:C10 etc.....

My question is if I clear either A1,B1 or C1, I also want to clear the
corresponding data in A5:A10, B5:B10, C5:C10 etc

If I only clear A1 & C1 then contents from A range & C range should only
clear. Any ideas would be appreciated.