on change event, funny behaviour

[JRRyan]

Hi,

I have a bit of code that triggers as part of a worksheet_change function. Here it is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 
If Not Intersect(Target, Range("H_23")) Is Nothing Then
If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False)) Then
Application.Run "AddQ1DescriptionMacro"
End If
End If
 
End Sub

Now, what is supposed to happen is - if I add some text to the cell named "H_23" then it will trigger the AddQ1DescriptionMacro and the cells contents will be pasted to a list on another worksheet. Where as if I delete existing contents from the same cell, nothing will trigger and no other change will be made beyond deleting the contents of the cell.

This works perfectly... except...

I have another bit of code designed to clear certain contents from the entire sheet and reset it back to default ready for a new bit of data. It looks something like this:

 Sub Clearing()
   Range("H_4_2").Select
    Selection.ClearContents
 
    Application.ScreenUpdating = False
 
    Range("RNG_1").Select
    Selection.ClearContents
    Range("RNG_2").Select
    Selection.ClearContents
    Range("H_17").Select
    Selection.ClearContents
    Range("H_19").Select
    Selection.ClearContents
    Range("F_1").Select
    Selection.ClearContents
    Range("F_2").Select
    Selection.ClearContents
    Range("RNG_3").Select
    Selection.ClearContents
    Application.ScreenUpdating = True
    ActiveWorkbook.Save
End Sub

The issue is this. If I manually delete one cell at a time, the code works fine. If, however, I delete a range of cells (as is the case with my named range "RNG_3" in the Clearing code) then the worksheet change triggers and adds/copies a blank line to my other sheet from every cell in the range.

I'm guessing it has to do with how intersect works with a range of cells as opposed to individual ones, but I'm kind of baffled as to how, or what I would do to change it.

My solution now is to list each cell in the range individually instead of referencing them as a range. Can anyone offer a better solution?

 

[njimack]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
[COLOR="Red"][B]Application.EnableEvents = False[/B][/COLOR]
'your code here
[COLOR="Red"][B]'Application.EnableEvents = True[/B][/COLOR]
End Sub

Also your second piece of code could be tidied up and made more efficient:

 Sub Clearing()
   Range("H_4_2").Select
    Selection.ClearContents
 
    Application.ScreenUpdating = False
 
    Range("RNG_1").ClearContents
    Range("RNG_2").ClearContents
    Range("H_17").ClearContents
    Range("H_19").ClearContents
    Range("F_1").ClearContents
    Range("F_2").ClearContents
    Range("RNG_3").ClearContents
    Application.ScreenUpdating = True
    ActiveWorkbook.Save
End Sub