Refactor VBA to make it work faster

[ouvay]

Hello,

I wrote this bit of code, but it takes a while to calculate considering how many times I use it - I believe it takes really long because it runs the code through the entire table column.

I am looking to try and get this to work faster, maybe by calculating only when there is a change or perhaps another way I am unaware of.

Please have a look at let me know how I may go about to doing this.

the code gives me the desired output.. It just takes its sweet time in doing so.

Sub checkProgress()
Application.ScreenUpdating = False

Dim i As Long

For i = 2 To Rows.Count

    If Range("C" & i).Value = "" Or Range("I" & i).Value <> "" Or Range("A" & i).Value <> "" Then
    Else
        Range("I" & i).Value = Application.WorksheetFunction.SumIfs(Range("G2:G" & i), Range("C2:C" & i), Range("C" & i), Range("D2:D" & i), Range("D" & i))
        Range("H" & i).Value = Range("E" & i).Value - Range("I" & i).Value
    End If
Next i

Application.ScreenUpdating = True
End Sub

Posting image for visual idea of what the code should do

 

[jolivanes]

See if the suggestions here make a difference.

Best Way To Improve VBA Macro Performance (Prevent Slow Code!)

I'm going to share with the rest of you a simple copy paste snippet of code to guarantee faster speeds in your code's execution. There are two steps. First, you have to copy the two subroutines below and paste them at the top of a module within your VBA Project.

www.thespreadsheetguru.com

 

[ouvay]

See if the suggestions here make a difference.

Best Way To Improve VBA Macro Performance (Prevent Slow Code!)

I'm going to share with the rest of you a simple copy paste snippet of code to guarantee faster speeds in your code's execution. There are two steps. First, you have to copy the two subroutines below and paste them at the top of a module within your VBA Project.

www.thespreadsheetguru.com

Thanks for suggesting that

I already have

Application.ScreenUpdating = False

in my code .. I'll go ahead and implement the rest of those too, but I doubt it would make a noticeable change.

My past research had shown me that turning ScreenUpdating off puts a real dent in processing time - the rest, not so much.. but doesn't hurt to try I suppose! ty

 

[Peter_SSs]

Welcome to the MrExcel board!

Yes, you are processing over a million rows when you are probably only using a very small (relatively) number.
It looks like that may well be a formal Excel table (ListObject). If that is so, give this a try with a copy of your workbook.
Assuming the table is the only table on the worksheet, or if more then this is ListObjects(1)

Sub checkProgress_v2()
  Dim i As Long
 
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects(1).DataBodyRange
    For i = 1 To .Rows.Count
        If .Range("C" & i).Value <> "" And .Range("I" & i).Value = "" And .Range("A" & i).Value = "" Then
            .Range("I" & i).Value = Application.WorksheetFunction.SumIfs(.Range("G1:G" & i), .Range("C1:C" & i), .Range("C" & i), .Range("D1:D" & i), .Range("D" & i))
            .Range("H" & i).Value = .Range("E" & i).Value - .Range("I" & i).Value
        End If
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

 

[ouvay]

Welcome to the MrExcel board!

Yes, you are processing over a million rows when you are probably only using a very small (relatively) number.
It looks like that may well be a formal Excel table (ListObject). If that is so, give this a try with a copy of your workbook.
Assuming the table is the only table on the worksheet, or if more then this is ListObjects(1)

Sub checkProgress_v2()
  Dim i As Long
 
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects(1).DataBodyRange
    For i = 1 To .Rows.Count
        If .Range("C" & i).Value <> "" And .Range("I" & i).Value = "" And .Range("A" & i).Value = "" Then
            .Range("I" & i).Value = Application.WorksheetFunction.SumIfs(.Range("G1:G" & i), .Range("C1:C" & i), .Range("C" & i), .Range("D1:D" & i), .Range("D" & i))
            .Range("H" & i).Value = .Range("E" & i).Value - .Range("I" & i).Value
        End If
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

Good grief that was insanely fast!!!

Thank you sooo much!!

 

[Peter_SSs]

Good grief that was insanely fast!!!

You're welcome, Thanks for the follow-up.