PDA

View Full Version : [SOLVED] clear contents of adjacent cell(s) on change



blackie42
12-05-2014, 08:06 AM
Hi,

I have a drop down list in column G, a dependent list in column H (using INDIRECT) and
another dependent list in column I (using SUBSTITUTE). 1000 Rows.

I'd like to clear columns H & I when column G is changed and also clear column I when only
column H is changed.

Any help appreciated

thanks
Jon

SamT
12-05-2014, 11:36 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Application.Enable Events = False
If Not Intersect(Target, Range("G:G")) Is Nothing Then
Range("H:I").ClearContents
GoTo AllDone
End If

If Not Intersect(Target, Range("H:H")) Is Nothing Then Range("I:I").ClearContents

AllDone:
Application.EnableEvents = True
End Sub

blackie42
12-08-2014, 02:00 AM
Apologies - might not have been specific enough.

The code bugs out if I leave the 'Application.EnableEvents' in andif I take out although it does work it clears everything i.e. the whole column(s)

What I wanted was something similar but for separate rows e.g. change G2 - H2 & I2 clear and not the whole columns, and if I change G14, only H2 & I2 would be affected.

thanks
Jon

SamT
12-08-2014, 04:00 AM
The code bugs out I don't understand.



it does work it clears everything i.e. the whole column(s) As planned.




change G2 - H2 & I2 clear and not the whole columns




Private Sub Worksheet_Change(ByVal Target As Range)
Application.Enable Events = False
If Not Intersect(Target, Range("G:G")) Is Nothing Then _
Range("H2:I2").ClearContents

Application.EnableEvents = True
End Sub

Aflatoon
12-08-2014, 06:56 AM
Perhaps you mean:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Enable Events = False
If Not Intersect(Target, Range("G:G")) Is Nothing Then
Target.Offset(, 1).Resize(, 2).ClearContents
GoTo AllDone
End If
If Not Intersect(Target, Range("H:H")) Is Nothing Then Target.Offset(, 1).Resize(, 1).ClearContents
AllDone:
Application.EnableEvents = True
End Sub

blackie42
12-08-2014, 08:10 AM
Thanks Guys - probably didn't explain too well.

Aflatoons extended code using resize does the job

regards
Jon

SamT
12-08-2014, 09:45 AM
Yeah, you really have to work on explaining.

clear columns H & I when column G is changed and also clear column I

change G2 - H2 & I2 clear and not the whole columns, and if I change G14, only H2 & I2 would be affected.

Aussiebear
12-08-2014, 11:54 AM
Are you missing an End If for the second If not intersect?

SamT
12-08-2014, 12:01 PM
@ Ted,

No, it's a one line If.