Suppress this first initialization of the private macro

[Greg Bonaparte]

I have a "private worksheet" below that monitors U30 THRU U629. My macro starts off by clearing all contents of U30 thru u629 prompting this private macro to initiate. I would like to suppress this first initialization of the private macro (since this is just a reset) and allow only subsequent activation:

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Range("$AW$21:$AX$629").AutoFilter Field:=1

If Not (Application.Intersect(Range(" U30:U629"), Target) Is Nothing) Then

If MsgBox("Select NO until ADDITIONAL SHARES are manually entered. Have you finished manually entering ADDITIONAL SHARES?", vbQuestion + vbYesNo, "") = vbYes Then
Call ManualCalculate
End If

End If

End Sub

[#2 · Accepted solution]

I assume you’re clearing the range in another macro? Before you clear, you’ll want to turn off events using
Application.EnableEvents = False

Then after you clear the range, reset Application.EnableEvents = True

[#3]

@MichaelMays 

Michael thank you! With your help my final code to fix this issue looks like this and is embedded in the macro that does the clearing of the contents. This worked perfectly:

Application.EnableEvents = False

Range("Z10,Z8,R2,T2:T27,U2:U11,V2:W5,U30:U629").Select
Range("U30").Activate
Application.CutCopyMode = False
Selection.ClearContents

Application.EnableEvents = True

[#4]

Glad it worked! Something you can also do to make code run more quickly is to avoid selecting and activating ranges. Instead, use: Range("Z10,Z8,R2,T2:T27,U2:U11,V2:W5,U30:U629").ClearContents

[#5]

@MichaelMays 

Wow I select and activate in multiple macros causing my entire process to take about 6mins. I always wondered how to make things run faster. I will begin implementing your suggestion. Ill get back to you soon with the result as I have much code to modify. Thanks

[#6]

Glad to hear it. One other idea you might already be doing is setting Application.ScreenUpdating = False at the beginning. Then at the end, set it back to true. This will definitely help it run faster