Autosum doesn't work

[bibbyd01]

Sorry for the torrent of questions today, I have lots and lots of things I'm learning at the moment!

I've created a simple macro to add an autosum to the bottom of a dynamic table. The table is created from other parts of the macro, and can vary in size.

I've created a variable that counts how many rows, but I'm stuck on adding it into creating the sum. This is what I've got so far

Dim rowsamount As Integer
Range("A2").Select
rowsamount = Range(Selection, Selection.End(xlDown)).Count
Range("B2").Select
ActiveCell.End(xlDown).Offset(2, 0).Select
Application.Sum (Range(-rowsamount, 0))

I also tried swapping the last line for ActiveCell.FormulaR1C1 = "=SUM(R[-rowsamount]C:R[-2]C) Based on recording an excel macro

How can I get it to sum correctly?

 

[VoG]

I'm not sure what range you are trying to sum. Here's how to sum from A2 to the last filled row in column A.

Sub Asum()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1).Value = WorksheetFunction.Sum(Range("A2:A" & LR))
End Sub

 

[shyrath]

if column A try

Sub sum1()

lastrow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row

sumcell = "A" & lastrow + 1
rng = "A2:A" & lastrow
Range(sumcell).Value = "=SUM(" & rng & ")"

End Sub

 

[bibbyd01]

Column A contains a list of people, including a row for the headings. After the end of the list there is a row space, then a row for the totals, then under the totals is some more data.

The reason I'm needing to specify the range that it's summing on is because if I used either a column letter(or reference) or xlUp then it will include the additional data below the totals.

So, I want it to specify a range to sum before selecting the cell that the data will appear into.

 

[VoG]

Try

Sub Asum()
Dim LR As Long
LR = Range("A2").End(xlDown).Row
Range("B" & LR + 1).Value = WorksheetFunction.Sum(Range("B2:B" & LR))
End Sub

 

[bibbyd01]

Thanks, works excellently, and more importantly, makes sense!!