Clumsy VBA code slows down Excel

BenRichards

New Member
Joined
Apr 7, 2014
Messages
41
Hi,

I've had some 25 of these kinds of IF-statements, and the worksheet has worked quickly:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
.
.
.

If Not Intersect(Target, Range("T82:AA457")) Is Nothing And ActiveSheet.Range("V80").Value = "-" Then
Application.EnableEvents = False
ActiveSheet.Range("T82:AA457").Value = ""
Application.EnableEvents = True
End If

However, I now added 25 other IFs, like this:
Code:
If Intersect(Target, Range("V80")) Is Nothing And ActiveSheet.Range("V80").Value = "-" Then
Application.EnableEvents = False
ActiveSheet.Range("T82:AA457").Value = ""
Application.EnableEvents = True
End If
As a result, whenever a cell is changed (any cell, not just the ones determined as the target ranges), Excel "thinks" for some five seconds and the mouse cursor indicates processing before the new cell value is displayed. Can someone help me?
 
Last edited by a moderator:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
It seems that there's something wrong with that lower IF. It doesn't matter if the changed cell is V80, if some cell is changed and V80 is "-", then the value change is triggered for that range. So even though I wanted to pinpoint that I wanted that range cleared only when cell V80 changed, it's done each time some cell changes. So the slowdown is the result of each 25 IF-functions working and so many ranges being emptied.

Can someone tell me how to alter that lower IF so that it only fires if the cell V80 changes?
 
Upvote 0
The intersect can only be cell V80 and its impossible for it to be nothing and to equal "-" so that code never fires. Put If Not is Nothing
 
Upvote 0
Andrew Poulsom, in the upper IF I don't want the user to enter anything in the range T82:AA457 if V80 is "-".

In the lower IF, in case the user has filled something already, but then V80 goes back to "-", I want that range cleared.

steve the fish, when I put NOT in there, then it doesn't clear the range when V80 goes to "-". If I keep it as it is, the range is cleared when V80 turns to "-" but it takes some five seconds.
 
Upvote 0
You are missing a Not, but try this syntax:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("T82:AA457")) Is Nothing And ActiveSheet.Range("V80").Value = "-" Then
        Application.EnableEvents = False
        ActiveSheet.Range("T82:AA457").Value = ""
        Application.EnableEvents = True
    ElseIf Not Intersect(Target, Range("V80")) Is Nothing And ActiveSheet.Range("V80").Value = "-" Then
        Application.EnableEvents = False
        ActiveSheet.Range("T82:AA457").Value = ""
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
Andrew Poulsom, if I enter that code, the first IF works but the second doesnt.

I can't enter anything in that range when V80 = "-" Works as wanted.
When V80, I can enter information in that range. Works as wanted.
If I change V80 back to "-", the range is not emptied. Doesn't work as wanted.

However, if I delete the NOT from the Elseif, the code works as it should:

When V80 turns to "-", the range is cleared.


I made the change to that format for all 25 sets of ranges I'm clearing with those IFs, but it didn't help with the processing time.
 
Upvote 0
This is how the code works for me.

If V80 doesn't contain "-" I can enter data in the range T82:AA457, otherwise I can't.
If I enter "-" in V80, T82:AA457 is cleared.

What's your entire code now?
 
Upvote 0
I know why it doesn't change with me. The cell V80 changes as a result of a formula (when I typed "-" in, the range changed). So instead of worksheet_change I think I should use worksheet_calculate. I'll have to test this and get back to you.

Private Sub Worksheet_Change(ByVal Target As Range) --> Private Sub Worksheet_Calculate()
 
Upvote 0
Now this gives the error "Object required." Does intersect not work with worksheet_calculate? Should it be If activecell = V80, and if so, how to write that in VBA?

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
If Intersect(Target, Range("AE80")) Is Nothing And ActiveSheet.Range("AE80").Value = "-" Then
Application.EnableEvents = False
ActiveSheet.Range("AC82:AJ457").Value = ""
Application.EnableEvents = True
End If
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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
Back
Top