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

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/

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