Sum dynamic columns

[VbaHell]

Hello all

I hope you can help on this

I have data values that starts in [Sheets("DashBoard").Range("C2")] with multiple rows and multiple columns.

Both the Rows and Columns can expand or contract in size every time the report is run

Using VBA how can I find the last column with data then sum the total of each row after the last column of data

Can anyone please help on this

 

[James006]

Hello,

Do not know the ' dynamic sum ' you might need ...

But you could have a Named Range dynamically defined ..

 

[VbaHell]

Hello,

Do not know the ' dynamic sum ' you might need ...

But you could have a Named Range dynamically defined ..

 

[VbaHell]

This is close to what I need

[Sub SumAllColumns()
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
For col = 3 To lastCol
lastRow = Cells(Rows.Count, col).End(xlUp).Row
colSum = WorksheetFunction.Sum(Range(Cells(2, col), Cells(lastRow, col)))
Cells(lastRow + 1, col) = colSum
Next col
End Sub]

How can I change this code please to find the last column and then add the total for each row.

I have been searching the web trying to find an answer but so far I can't find what I need

 

[mohadin]

Hi
Try

Sub test()
    Dim i As Double
    Dim a, b
    a = Cells(1).Offset(1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1, Cells(2, Columns.Count).End(xlToLeft).Column)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a)
        b(i) = SumNRowsAtXColumnInArray(Application.Transpose(a), i)
    Next
    Cells(1, 1).Offset(1, UBound(a, 2)).Resize(UBound(b)) = Application.Transpose(b)
End Sub
Function SumNRowsAtXColumnInArray(varArray As Variant, x As Double) As Variant
    Dim lngLoop As Long
    Dim varSum
    For lngLoop = 1 To UBound(varArray, 1)
        varSum = varSum + varArray(lngLoop, x)
    Next lngLoop
    SumNRowsAtXColumnInArray = varSum
    lngLoop = Empty
    varSum = Empty
End Function

 

[mumps]

Try:

Sub SumRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, lCol As Long, x As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For x = 1 To LastRow
        lCol = Cells(x, Columns.Count).End(xlToLeft).Column
        Cells(x, lCol + 1) = WorksheetFunction.Sum(Range("A" & x).Resize(, lCol))
    Next x
    Application.ScreenUpdating = True
End Sub

 

[VbaHell]

Mumps thank you very much for your help on this, this works great

 

[VbaHell]

Hi
Try

Sub test()
    Dim i As Double
    Dim a, b
    a = Cells(1).Offset(1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1, Cells(2, Columns.Count).End(xlToLeft).Column)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a)
        b(i) = SumNRowsAtXColumnInArray(Application.Transpose(a), i)
    Next
    Cells(1, 1).Offset(1, UBound(a, 2)).Resize(UBound(b)) = Application.Transpose(b)
End Sub
Function SumNRowsAtXColumnInArray(varArray As Variant, x As Double) As Variant
    Dim lngLoop As Long
    Dim varSum
    For lngLoop = 1 To UBound(varArray, 1)
        varSum = varSum + varArray(lngLoop, x)
    Next lngLoop
    SumNRowsAtXColumnInArray = varSum
    lngLoop = Empty
    varSum = Empty
End Function

Mohadin thank you for your reply

 

[mumps]

You are very welcome.

 

[mohadin]

You are well come