Strange behavior by Worksheet_Change event

[LuckyDPR]

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveCell.Row = 1 Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Not (Application.Intersect(Target, Range("Product[Model]")) Is Nothing) Then              'Say Procedure First
        'Statements
    End If
    If Not (Application.Intersect(Target, Range("Product[Serial]")) Is Nothing) Then              'Say Procedure Second
        'Statements
    End If
    If Not (Application.Intersect(Target, Range("Product[DOP]")) Is Nothing) Then              'Say Procedure Third
        'Statements
    End If
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

in above code when worksheet_change event is triggered, it takes extra time (Say 2 Seconds) to run PROCEDURE First, whereas PROCDURE Second and PROCEDURE Third runs instantaneously, I have tried above procedures without any statement (DO NOTHING), still results are same, so it is not a problem with codes under these procedures

 

[Joe4]

Without having access to your workbook, it is hard to say.
What is the exact size of each range?

Also note that in each case, all three IF statements will always run (it will always check all 3 conditions, even if the first one is met).
You might want to try using "Else If" statements, so that if the first runs, the second two don't.

 

[LuckyDPR]

Without having access to your workbook, it is hard to say.
What is the exact size of each range?

Also note that in each case, all three IF statements will always run (it will always check all 3 conditions, even if the first one is met).
You might want to try using "Else If" statements, so that if the first runs, the second two don't.

Range("Product[Model]") have too many dependent cells
whereas other two ranges does not have that many
also my table have around 10000 Rows, but i am wondering if we make the calculations off than ever will it take somehow long because of those dependencies, there is no other difference other than this in these ranges, because as soon as I press enter after typing data in product[Model] Range it take some time to get to the respective IF statement

 

[Joe4]

Range("Product[Model]") have too many dependent cells
whereas other two ranges does not have that many

Well then, I think that probably answers your question, doesn't it?
Quite frankly, if you have around 10000 rows and lots of dependent cells, 2 seconds doesn't seem too unreasonable.

It sounds to me like you might really be dealing with a Relational Database model (whenever I hear of many rows of inter-dependent data, that is often the case).
As such, a Relational Database product like Microsoft Access or SQL may be better suited for your project.
Or perhaps you may want to look at using Power Query if you want to continue to do it in Excel.

 

[LuckyDPR]

R

Well then, I think that probably answers your question, doesn't it?
Quite frankly, if you have around 10000 rows and lots of dependent cells, 2 seconds doesn't seem too unreasonable.

It sounds to me like you might really be dealing with a Relational Database model (whenever I hear of many rows of inter-dependent data, that is often the case).
As such, a Relational Database product like Microsoft Access or SQL may be better suited for your project.
Or perhaps you may want to look at using Power Query if you want to continue to do it in Excel.

RDBMS, may be, but I don't have that much courage, energy and expertise to do that, apart from that my primary data (Raw Data) Comes from copy paste like operation (although automated), also in excel I can look at data in a glance, hence this approach.

 

[Joe4]

RDBMS, may be, but I don't have that much courage, energy and expertise to do that, apart from that my primary data (Raw Data) Comes from copy paste like operation (although automated), also in excel I can look at data in a glance, hence this approach.

OK, that is fine, but then just be aware that you may have to live with a few of the minor inconveniences of using Excel for purposes for which it was really not designed for.
It can do RDBMS, but since that is not what it was designed for, it can often be a bit clumsy and cumbersome to do so. It is not the best tool for the job, so doesn't handle things as gracefully as a program designed for that purpose.