Excel VBA SUMIF on 50 lines how to shorten the code

Question

I have a code that repeats itself on 50 lines in total but most of the time it only needs to repeat itself for 10 to 20 lines, I'm wondering if there is a way to check how many rows are populated and just run the code for that amount of rows instead of running it against 50 lines?

Range("L2").Select
ActiveCell.Formula = "=IF(J2="""","""",SUMIFS(E:E,D:D,J2))"
Range("L3").Select
ActiveCell.Formula = "=IF(J3="""","""",SUMIFS(E:E,D:D,J3))"
Range("L4").Select
ActiveCell.Formula = "=IF(J4="""","""",SUMIFS(E:E,D:D,J4))"
Range("L5").Select
ActiveCell.Formula = "=IF(J5="""","""",SUMIFS(E:E,D:D,J5))"
Range("L6").Select
ActiveCell.Formula = "=IF(J6="""","""",SUMIFS(E:E,D:D,J6))"
Range("L7").Select
ActiveCell.Formula = "=IF(J7="""","""",SUMIFS(E:E,D:D,J7))"
Range("L8").Select
ActiveCell.Formula = "=IF(J8="""","""",SUMIFS(E:E,D:D,J8))"
Range("L9").Select
ActiveCell.Formula = "=IF(J9="""","""",SUMIFS(E:E,D:D,J9))"
Range("L10").Select
ActiveCell.Formula = "=IF(J10="""","""",SUMIFS(E:E,D:D,J10))"
Range("L11").Select
ActiveCell.Formula = "=IF(J11="""","""",SUMIFS(E:E,D:D,J11))"
Range("L12").Select
ActiveCell.Formula = "=IF(J12="""","""",SUMIFS(E:E,D:D,J12))"
Range("L13").Select
ActiveCell.Formula = "=IF(J13="""","""",SUMIFS(E:E,D:D,J13))"
Range("L14").Select
ActiveCell.Formula = "=IF(J14="""","""",SUMIFS(E:E,D:D,J14))"
Range("L15").Select
ActiveCell.Formula = "=IF(J15="""","""",SUMIFS(E:E,D:D,J15))"
Range("L16").Select
ActiveCell.Formula = "=IF(J16="""","""",SUMIFS(E:E,D:D,J16))"
Range("L17").Select
ActiveCell.Formula = "=IF(J17="""","""",SUMIFS(E:E,D:D,J17))"
Range("L18").Select
ActiveCell.Formula = "=IF(J18="""","""",SUMIFS(E:E,D:D,J18))"
Range("L19").Select
ActiveCell.Formula = "=IF(J19="""","""",SUMIFS(E:E,D:D,J19))"
Range("L20").Select
ActiveCell.Formula = "=IF(J20="""","""",SUMIFS(E:E,D:D,J20))"
Range("L21").Select
ActiveCell.Formula = "=IF(J21="""","""",SUMIFS(E:E,D:D,J21))"
Range("L22").Select
ActiveCell.Formula = "=IF(J22="""","""",SUMIFS(E:E,D:D,J22))"
Range("L23").Select
ActiveCell.Formula = "=IF(J23="""","""",SUMIFS(E:E,D:D,J23))"
Range("L24").Select
ActiveCell.Formula = "=IF(J24="""","""",SUMIFS(E:E,D:D,J24))"
Range("L25").Select
ActiveCell.Formula = "=IF(J25="""","""",SUMIFS(E:E,D:D,J25))"
Range("L26").Select
ActiveCell.Formula = "=IF(J26="""","""",SUMIFS(E:E,D:D,J26))"
Range("L27").Select
ActiveCell.Formula = "=IF(J27="""","""",SUMIFS(E:E,D:D,J27))"
Range("L28").Select
ActiveCell.Formula = "=IF(J28="""","""",SUMIFS(E:E,D:D,J28))"
Range("L29").Select
ActiveCell.Formula = "=IF(J29="""","""",SUMIFS(E:E,D:D,J29))"
Range("L30").Select
ActiveCell.Formula = "=IF(J30="""","""",SUMIFS(E:E,D:D,J30))"
Range("L31").Select
ActiveCell.Formula = "=IF(J31="""","""",SUMIFS(E:E,D:D,J31))"
Range("L32").Select
ActiveCell.Formula = "=IF(J32="""","""",SUMIFS(E:E,D:D,J32))"
Range("L33").Select
ActiveCell.Formula = "=IF(J33="""","""",SUMIFS(E:E,D:D,J33))"
Range("L34").Select
ActiveCell.Formula = "=IF(J34="""","""",SUMIFS(E:E,D:D,J34))"
Range("L35").Select
ActiveCell.Formula = "=IF(J35="""","""",SUMIFS(E:E,D:D,J35))"
Range("L36").Select
ActiveCell.Formula = "=IF(J36="""","""",SUMIFS(E:E,D:D,J36))"
Range("L37").Select
ActiveCell.Formula = "=IF(J37="""","""",SUMIFS(E:E,D:D,J37))"
Range("L38").Select
ActiveCell.Formula = "=IF(J38="""","""",SUMIFS(E:E,D:D,J38))"
Range("L39").Select
ActiveCell.Formula = "=IF(J39="""","""",SUMIFS(E:E,D:D,J39))"
Range("L40").Select
ActiveCell.Formula = "=IF(J40="""","""",SUMIFS(E:E,D:D,J40))"
Range("L41").Select
ActiveCell.Formula = "=IF(J41="""","""",SUMIFS(E:E,D:D,J41))"
Range("L42").Select
ActiveCell.Formula = "=IF(J42="""","""",SUMIFS(E:E,D:D,J42))"
Range("L43").Select
ActiveCell.Formula = "=IF(J43="""","""",SUMIFS(E:E,D:D,J43))"
Range("L44").Select
ActiveCell.Formula = "=IF(J44="""","""",SUMIFS(E:E,D:D,J44))"
Range("L45").Select
ActiveCell.Formula = "=IF(J45="""","""",SUMIFS(E:E,D:D,J45))"
Range("L46").Select
ActiveCell.Formula = "=IF(J46="""","""",SUMIFS(E:E,D:D,J46))"
Range("L47").Select
ActiveCell.Formula = "=IF(J47="""","""",SUMIFS(E:E,D:D,J47))"
Range("L48").Select
ActiveCell.Formula = "=IF(J48="""","""",SUMIFS(E:E,D:D,J48))"
Range("L49").Select
ActiveCell.Formula = "=IF(J49="""","""",SUMIFS(E:E,D:D,J49))"
Range("L50").Select
ActiveCell.Formula = "=IF(J50="""","""",SUMIFS(E:E,D:D,J50))"

Thank you in advance!

Look up how to find the last row. And read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Sep 29 '20 at 10:10
`Range("L2:L" & Cells(rows.count, "J").end(xlup).row).Formula = "=IF(J2="""","""",SUMIFS(E:E,D:D,J2))"` should be enough. — Rory, Sep 29 '20 at 10:13
OMG! Please, remove all lines with `Range(...).Select`. Avoid of using it, due to tons of reason! — Maciej Los, Sep 29 '20 at 10:15

score 2 · Answer 1 · edited Sep 29 '20 at 10:37

No need to loop through rows - wastes precious time. Apologies for any formatting problems - hard to write code on a phone.

Sub Test()

    With ThisWorkbook.Worksheets("Sheet1")
        Dim lRow As Long
        lRow = .Cells(.Rows.Count, 10).End(xlUp).Row 'Last row in column 10.
        'Put formula in column 12, row 2 to last row.
        'RC[-2] means 'this row, two columns to the left'
        'C5 means 'all of column 5'
        .Range(.Cells(2, 12), .Cells(lRow, 12)).FormulaR1C1 = _
            "=IF(RC[-2]="""","""",SUMIFS(C5,C4,RC[-2]))"
    End With

End Sub

score 1 · Accepted Answer · answered Sep 29 '20 at 10:18

Please, read my comment to the question first.

The simplest way to achieve that is to use For... Next loop:

Dim i As Integer, j As Integer
'find last row
j = ActiveSheet.Range("L" & ActiveSheet.Rows.Count).End(xlUp).Row 
For i = 2 To j
    Range("L" & i).Formula = "=IF(J" & i & "="""","""",SUMIFS(E:E,D:D,J" & i & "))"
Next

For further details, please see: For...Next statement (VBA)

score 1 · Answer 3 · answered Sep 29 '20 at 10:24

It's not very difficult. Go to File-Options-Formulas and check R1C1 reference style

All your formulas

=IF(J2="";"";SUMIFS(E:E;D:D;J2))
=IF(J3="";"";SUMIFS(E:E;D:D;J3))
=IF(J4="";"";SUMIFS(E:E;D:D;J4))
=IF(J5="";"";SUMIFS(E:E;D:D;J5))
=IF(J6="";"";SUMIFS(E:E;D:D;J6)) etc

will change to the same formula

=IF(RC[-2]="";"";SUMIFS(C[-7];C[-8];RC[-2]))

This means that all your code can be replaced with one line

Range("L2:L50").Formula2R1C1 = "=IF(RC[-2]="""","""",SUMIFS(C[-7],C[-8],RC[-2]))"

Excel VBA SUMIF on 50 lines how to shorten the code

3 Answers3