Autosum macro for Excel

[china99boy]

Hi all,

I have a query that is exported to an excel template. Everytime my data is exported to excel I may get more or less rows of information. What I am trying to do is create a macro or any way that would automate the process of placing the total for each column at the bottom. I could use the autosum function but the amount of rows are always changing. Once the data is imported into excel, I want excel to move to the last row and insert the sum for each column. Please help me with the best way to accomplish this task.

 

[HaHoBe]

Hi, china99boy,

in Excel this could be achieved by using a loop like this:

Option Explicit

Sub AutoSumLoop()
Dim lngColumn As Long
Dim lngLastRow As Long
For lngColumn = 1 To Range("A1").End(xlToRight).Column
  lngLastRow = Cells(1, lngColumn).End(xlDown).Row
  Cells(lngLastRow + 1, lngColumn).Formula = "=Sum(" & Cells(1, lngColumn).Address & ":" & _
      Cells(lngLastRow, lngColumn).Address & ")"
Next lngColumn
End Sub

Ciao,
Holger

 

[china99boy]

Autosum Colums

Thanks for your help.

I tried your codes but came up with errors. It stops at

Cells(lngLastRow + 1, lngColumn).Formula = "=Sum(" & Cells(1, lngColumn).Address & ":" & _
Cells(lngLastRow, lngColumn).Address & ")"

and produces a run-time error '1004'


I found this post on another forum which works but has a minor problem. Can you help.

Sub QuickTotals()

r = ActiveCell.CurrentRegion.Rows.Count
Set SumRow = ActiveCell.CurrentRegion.Offset(r, 0).Resize(1)
SumRow.FormulaR1C1 = "=SUM(R[" & -r & "]C:R[-1]C)"
SumRow.Offset(0, -1).Resize(1, 1).Value = "Totals"

End Sub


I have columns that range from "A" all the way to "Z". Column A has the names of employees and B to Z has the numbers to be sum. When I run the above code it sums the totals from B to Z perfectly but it also places a Zero "0" in column A which I would like it to place the heading "Totals" instead. The code should do that, but instead I get a Run-time error '1001': Application-defined or object-defined error that stops at the portion highlighted in red and does not place the "Total" text in Column A.

How do I get the code to ignore column A, but place the text "Total" in that field instead of the zero.Thanks for any help that you may have.

 

[HaHoBe]

Hi, china99boy,

I´m afraid I can´t reproduce the run tim error you mentioned - at least not with my workbook created for this thread (but that´s as simple as can be: no merged cells, no gimmicks, no nothing but values to count... and now a column for the names)

Regarding your question: alter the last line of the code to

SumRow.Cells(1).Value = "Totals"

Ciao,
Holger

 

[china99boy]

That changed in the last line worked like a champ. Thanks a million.