Sum column values and insert result in last cell with VBA
 

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

--
Les

Sum column values and insert result in last cell with VBA
 

On May 22, 1:39 pm, Les wrote:
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

--
Les

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/

Sum column values and insert result in last cell with VBA
 

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)



"Les" wrote in message
...
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

--
Les