Excel Formula

[hrayani]

Hello Friends,

I am using this formula in a worksheet on 120 cells. And the sheets is set to hide/unhide rows based on the results.
The formula is giving correct result but it is taking 4 to 5 seconds for the sheet to hide/unhide rows.

Can anyone pls suggest another option that I could replace the below formula with which would work faster

=SUMPRODUCT(SUMIFS(r_daily_production_cutting,r_daily_production_po,$U$8:$U$22,r_daily_production_date,$B228))

Regards,

Humayun

 

[eduzs]

Only viewing the code is it possible to analyze.
In VBA a basic tip is to include
at start:
application.screenupdating=false
at end:
application.screenupdating=true

 

[hrayani]

Only viewing the code is it possible to analyze.

sure, why not

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="merchant"
Worksheets("CURRENT PRODUCTION STATUS").EnableCalculation = True
  Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "1", , , 0
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Range("C32").ClearContents
Range("A:T").Select 'set range zoom
ActiveWindow.Zoom = True
Range("B7").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingRows:=True, Password:="merchant"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
     Application.ScreenUpdating = False
        
    If Not Intersect(Target, Range("B7, B226")) Is Nothing Then
   
    ActiveSheet.Unprotect Password:="merchant"
   
    Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "1", , , 0
   
    Range("C3").ShrinkToFit = True
    Range("B26:B29").ShrinkToFit = True
   
    Range("C32").ClearContents
    Target.Cells.Select
   
      ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingRows:=True, Password:="merchant"
     
    Application.ScreenUpdating = True
    End If
End Sub

 

[eduzs]

Move application.screenupdating=true to out of if/then statment.
I don't see what can be more optimized, perhaps the formulas in the cells are complex.
Maybe someone else can help.

 

[hrayani]

Move application.screenupdating=true to out of if/then statment.
I don't see what can be more optimized, perhaps the formulas in the cells are complex.
Maybe someone else can help.

I Tried everything but to no avail

Maybe some other member can shed some light on the issue

Thanks for your time