I have a function that exports rows of data to an Excel spreadsheet template.
Is there a way to dynamically show the sum of each column after the last row
of data since my function may not always export the same amount of rows. I
always want to show the sum to the rows after the last row of data. Thanks.

Does your sum cell have to be below the last row of data or can you create a
sum area just above the header row? Then you can use (i.e.) =SUM(b5:b65536)
and all your data will be captured whether you add/remove rows, etc.

"Akilah" wrote:

I have a function that exports rows of data to an Excel spreadsheet template.
Is there a way to dynamically show the sum of each column after the last row
of data since my function may not always export the same amount of rows. I
always want to show the sum to the rows after the last row of data. Thanks.

You will have to use a macro to do this. Try the below which will put the
autosum for all the columns..If you are new to macros set the Security level
to low/medium in (Tools|Macro|Security). From workbook launch VBE using
short-key Alt+F11. From menu 'Insert' a module and paste the below code.
Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro()


Sub Macro1()
Dim lngCol As Long
Dim lngLastRow As Long
Dim lngLastCol As Long
'Find last Row in ColumnA
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
'Find last Col in the last Row
lngLastCol = ActiveSheet.Cells(lngLastRow, _
Columns.Count).End(xlToLeft).Column
'Loop until last Column
For lngCol = 1 To lngLastCol
'Fill the cells with formula
Cells(lngLastRow + 1, lngCol).FormulaR1C1 = "=SUM(R[-" & lngLastRow &
"]C:R[-1]C)"
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Akilah" wrote:

I have a function that exports rows of data to an Excel spreadsheet template.
Is there a way to dynamically show the sum of each column after the last row
of data since my function may not always export the same amount of rows. I
always want to show the sum to the rows after the last row of data. Thanks.

Hi,

Since we don't know how your data is laid out we can't propose the actual
code but here is an example in which you select the top row of the cells you
want to sum:

Sub AddFormula()
Dim Bot As String
Dim Top As String
Dim cell As Range
For Each cell In Selection
Bot = cell.End(xlDown).Address
Top = cell.Address
Range(Bot).Offset(1, 0) = "=SUM(" & Top & ":" & Bot & ")"
Next cell
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Akilah" wrote:

I have a function that exports rows of data to an Excel spreadsheet template.
Is there a way to dynamically show the sum of each column after the last row
of data since my function may not always export the same amount of rows. I
always want to show the sum to the rows after the last row of data. Thanks.