Worksheet_Change and Inersect code hangs if row is deleted

Atholl

Active Member
Joined
May 19, 2002
Messages
434
Hello all,

The code below performs a function if any cell in the range G5:G1000 is altered, and it works very well. However, sometimes I need to delete rows which intersect the range. When I do this, Excel hangs and the only way to exit this is to click to 'X' in the outermost top right of the screen to exit Excel, then when it asks if I want to save changes, I click cancel and things are back to normal. Can anyone offer any suggestions to avoid the hang up when a row is deleted?

Atholl



Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
    On Error GoTo err_fix
   

If Not Intersect(Target, Range("G5:G1000")) Is Nothing Then
If Target.Value = "ATM" Then MsgBox "Please ensure that Task is allocated", vbInformation, "ATR"
    
End If

Application.ScreenUpdating = True
    Exit Sub
   
err_fix:
    If Err.Number = 13 Then
        Exit Sub
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
Application.ScreenUpdating = True

End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Atholl

Not certain that this will work for you (I haven't replicated the error you are seeing) but try inserting:

Code:
If Target.Count>1 Then Exit Sub

Below the Sub name.

Best regards

Richard
 

Atholl

Active Member
Joined
May 19, 2002
Messages
434
Hi Richard,

It now works perfectly; thanks as always :biggrin:

Atholl
 

Forum statistics

Threads
1,137,205
Messages
5,680,184
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top