Sum Dynamic number of Rows

[Viking1221]

Hello,

I am looking for help with a macro that will sum columns D:CZ, in the rows starting at row 10 and only those with values. The number of columns and rows will never be the same from week to week. For example, one file may have data in columns D:AA and only 1000 rows, while the next one has data in columns D:BZ and 2,500 rows. This formula will need to be added by a macro as the sheet is completely erased with a macro before being updated with new data each week.

 

[alansidman]

Within each week, will each column have the same number of rows?

 

[Viking1221]

Within each week, will each column have the same number of rows?

Alan,

Yes, every column will always have the same number of rows.

Thanks

 

[Viking1221]

Alan,

Yes, every column will always have the same number of rows.

Thanks

Anyone have any ideas?

 

[alansidman]

Option Explicit


Sub NewSums()
    Dim lr As Long, lc As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    lc = Cells(10, Columns.Count).End(xlToLeft).Column
    Dim i As Long
    For i = 4 To lc
        Cells(lr + 1, i) = WorksheetFunction.Sum(Range(Cells(10, i), Cells(lr, i)))
    Next i
End Sub

 

[Viking1221]

Option Explicit


Sub NewSums()
    Dim lr As Long, lc As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    lc = Cells(10, Columns.Count).End(xlToLeft).Column
    Dim i As Long
    For i = 4 To lc
        Cells(lr + 1, i) = WorksheetFunction.Sum(Range(Cells(10, i), Cells(lr, i)))
    Next i
End Sub

This works great, but it is adding up each column, I need one to add up each row. Sorry if I was not clear. I assume it would be easy to figure out, but as I am just starting to learn VBA are you able to provide code that does this as well?

 

[alansidman]

Option Explicit


Sub NewSums()
    Dim lr As Long, lc As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    lc = Cells(10, Columns.Count).End(xlToLeft).Column
    Dim i As Long
    For i = 10 To lr
        Cells(i, lc + 1) = WorksheetFunction.Sum(Range(Cells(i, "D"), Cells(i, lc)))
    Next i
End Sub

 

[DanteAmor]

This works great, but it is adding up each column, I need one to add up each row. Sorry if I was not clear. I assume it would be easy to figure out, but as I am just starting to learn VBA are you able to provide code that does this as well?

Where do you want the formula? in column c?
Do you have titles in row 9?
Do you always have values ​​in column D?

Try this:

[SIZE=1]Sub Macro5()[/SIZE]
[SIZE=1]  Range("C10:C" & Range("D" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUM(RC[1]:RC[" & Cells(9, Columns.Count).End(xlToLeft).Column & "])"[/SIZE]
[SIZE=1]End Sub[/SIZE]