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
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
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
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?
@ Ted,
No, it's a one line If.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.