Sum column values and insert result in last cell with VBA

[Les]

Hi all, This is probably so simple but it is eluding me...

Column "AL" has a variable number of values in it and i need to get the
total and paste only the total in the cell below the last used cell.

Could somebody please help me out of my misery... :-0)

Thnks in advance

 

[T Lavedas]

Hi all, This is probably so simple but it is eluding me...

Column "AL" has a variable number of values in it and i need to get the
total and paste only the total in the cell below the last used cell.

Could somebody please help me out of my misery... :-0)

Thnks in advance

Here is one way I can think of ...

Sub SumColumn()
Dim sCol, sRow
Range("AL1").EntireColumn.Cells(65536, 1).Select
ActiveCell.End(xlUp).Select
sCol = Split(ActiveCell.Address, "$")(1)
sRow = ActiveCell.Row
' Creates a formula that sums column from row 1 to last occupied
cell
ActiveCell.Cells(2, 1) = "=SUM(" & sCol & "1:" & sCol & sRow & ")"
' Optional - converts cell from formula to its result
' ActiveCell.Cells(2, 1) = ActiveCell.Cells(2, 1)
End Sub

This acts on currently active worksheet and fails if the active object
is not a worksheet (such as a chartsheet).

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

 

[Chip Pearson]

Try

With ActiveSheet.Cells(Rows.Count, "AL").End(xlUp)(2, 1)
.Formula = "=SUM(AL1:" & .Offset(-1, 0).Address & ")"
End With


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)