Excel Crashing on one line of code

bassmaninaus · Mar 10th 2022

I have a Worksheet Change code, below being the start, however when executing the last line, excel shuts down/closes. 'If Not Intersect(Target, MyRange) is Nothing Then'

Can anyone see anything wrong with the below code?

Thanks in advance.

Code

Private Sub Worksheet_Change(ByVal Target As Range)


Dim MyRange As Range
Set MyRange = Range("B14:B1048576")


Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


If Target.Cells.Count > 1 Then GoTo exitHandler

If Not Intersect(Target, MyRange) Is Nothing Then

Display More

**KjBox** · Mar 10th 2022

Maybe

Code

Private Sub Worksheet_Change(ByVal Target As Range)


Dim MyRange As Range, lRow As Long
lRow = Cells(Rows.Count, 2).End(xlUp).Row
Set MyRange = Cells(14, 2).Resize(lRow - 14)


Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


If Target.Cells.Count > 1 Then GoTo exitHandler

If Not Intersect(Target, MyRange) Is Nothing Then

Display More

bassmaninaus · Mar 10th 2022

Thanks KjBox. I was playing around with it and changed the code below and its no longer crashing. No idea why you'd need to say what sheet the range is on as you have to be on the same sheet when runng a worksheet change macro..

Thanks though, if I have any other issues I'll try your code.

Code

Set MyRange = Range("B14:B1048576") changed to 
Set MyRange = sheet1.Range("B14:B1048576")

**KjBox** · Mar 10th 2022

If you have another sheet active and a change on that active sheet causes Sheet1 Column B to change then yes, you will have to specify the Sheet

**rory** · Mar 11th 2022

Assuming Sheet1 is the sheet with the change event in it, there is no technical difference between the original code and the code with the sheet specified, so I don't see why it would have fixed anything to be honest.

Excel Crashing on one line of code

Participate now!

Share

Similar Threads

How do I run my macro only on one specific sheet?

Extracting Data from Text File - Identify starting point then skip down x number of lines to get to first line to extract

Counting quantity - data contained in one cell by new line

VBA Code to work across mutli sheet in one workbook

Help Required for only one line of code

Vba line of code takes a long time to run

Tags