Sum - to end

[Annette]

Since I don't always know how many rows I will have in a spreadsheet, I
would like a macro that if I run the macros on a specific column, it
automatically takes me to the end the filled column and drop down one more
row to a blank cell and adds the entire column.

My macro is cell specific using recorder and I don't want that.

Thanks much for assistance!

 

[Don Guillett]

sub sumtoend()
ac=activecell.column
lr=cells(rows.count,ac).end(xlup).row
cells(lr+2,ac)=application.sum(range(cells(1,ac),cells(lr,ac))
end sub

 

[Mike H]

Hi,

These couple of lines put a sum formula in the first cell below the data in
column A

Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row
Cells(LastRow + 1, 1).Formula = "=sum(A1:A" & LastRow & ")"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Don Guillett]

run the macros on a specific column

 

[L. Howard Kittle]

Give this a try. Click on the column letter and run.

Sub SumAColumn()
Dim i As Long
Dim j As Long
Dim Drng As Range
Set Drng = Selection
j = Application.WorksheetFunction.CountA(Drng)
i = Application.WorksheetFunction.Sum(Drng)
Drng.End(xlDown).Offset(j, 0).Value = i
End Sub

HTH
Regards,
Howard

 

[L. Howard Kittle]

After further review, I belief this will only work well if there is at least
one blank cell above the column of numbers to be summed. Otherwise the
total is offset below the last value in the column by the number of values
in the column.

Regards,
Howard