Macro Totaling a Column of Numbers
 

OK, all, here's the scenario: I have a spreadsheet with 20 of columns.
Half of the columns have numbers in them. The columns are of different
lengths so some have 35 rows, some have 65 rows. I want to have a
macro whereby I can click on the first cell in the column (ie Colmn B)
that has a digit and the macro will add all the numbers in that column
and place the total at the next available cell at the bottom of the
column (ie in cell B37). Then, I want to move to a DIFFERENT column
(Column D) that has 65 numbers in it, run the macro and it will also
add up all the numbers in THAT column and place the total in the next
available cell (Cell D67) at the bottom of the column. And on and on
withthe rest of the columns with numbers. Does my question make sense
and is it possible?

Macro Totaling a Column of Numbers
 

Colibri

Not sure I totally understand but you could use a Worksheet_Selection change
event,like below. (To invoke right click the sheet tab with the data and
select view code... and paste here). This will add a total to the last entry
on a column that is clicked on providing the data in the column is
contiguous (no gaps)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim colNum As Integer, lLastRow As Long
Dim dTotal As Double
colNum = Target.Column
lLastRow = Application.WorksheetFunction.CountA(Columns(Targe t.Column))
dTotal = Application.WorksheetFunction.Sum(Columns(Target.C olumn))
Cells(lLastRow + 1, Target.Column).Value = dTotal
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


wrote in message
oups.com...
OK, all, here's the scenario: I have a spreadsheet with 20 of columns.
Half of the columns have numbers in them. The columns are of different
lengths so some have 35 rows, some have 65 rows. I want to have a
macro whereby I can click on the first cell in the column (ie Colmn B)
that has a digit and the macro will add all the numbers in that column
and place the total at the next available cell at the bottom of the
column (ie in cell B37). Then, I want to move to a DIFFERENT column
(Column D) that has 65 numbers in it, run the macro and it will also
add up all the numbers in THAT column and place the total in the next
available cell (Cell D67) at the bottom of the column. And on and on
withthe rest of the columns with numbers. Does my question make sense
and is it possible?

Macro Totaling a Column of Numbers
 

Thanks, Nick, for the quick response. You gave me some thoughts and
what we morphed your suggestion into is this:
AddacolumnUp Macro
' Macro recorded 3/21/2007 by Colibri
Dim colNum As Integer, lLastRow As Long
Dim dTotal As Double
Dim Targetcolumn As String

Targetcolumn = ActiveCell.Column
colNum = Targetcolumn
lLastRow =
Application.WorksheetFunction.CountA(Columns(Activ eCell.Column))
dTotal = Application.WorksheetFunction.Sum(Columns(ActiveCe ll.Column))
Cells(lLastRow + 1, ActiveCell.Column).Value = dTotal
End Sub

To try it, open Excel and in column A, insert a series of numbers in
cells A1:A5. Do the same in cells D1:D7. Click on cell A1 and run the
macro. Click on Cell D1 and run the macro..you'll get the point/idea.

Thanks, again!


On Mar 21, 3:15 pm, "Nick Hodge"
wrote:
Colibri

Not sure I totally understand but you could use a Worksheet_Selection change
event,like below. (To invoke right click the sheet tab with the data and
select view code... and paste here). This will add a total to the last entry
on a column that is clicked on providing the data in the column is
contiguous (no gaps)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim colNum As Integer, lLastRow As Long
Dim dTotal As Double
colNum = Target.Column
lLastRow = Application.WorksheetFunction.CountA(Columns(Targe t.Column))
dTotal = Application.WorksheetFunction.Sum(Columns(Target.C olumn))
Cells(lLastRow + 1, Target.Column).Value = dTotal
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
e.co.uk

wrote in message

oups.com...



OK, all, here's the scenario: I have a spreadsheet with 20 of columns.
Half of the columns have numbers in them. The columns are of different
lengths so some have 35 rows, some have 65 rows. I want to have a
macro whereby I can click on the first cell in the column (ie Colmn B)
that has a digit and the macro will add all the numbers in that column
and place the total at the next available cell at the bottom of the
column (ie in cell B37). Then, I want to move to a DIFFERENT column
(Column D) that has 65 numbers in it, run the macro and it will also
add up all the numbers in THAT column and place the total in the next
available cell (Cell D67) at the bottom of the column. And on and on
withthe rest of the columns with numbers. Does my question make sense
and is it possible?- Hide quoted text -

- Show quoted text -

Macro Totaling a Column of Numbers
 

Great

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


wrote in message
ups.com...


Thanks, Nick, for the quick response. You gave me some thoughts and
what we morphed your suggestion into is this:
AddacolumnUp Macro
' Macro recorded 3/21/2007 by Colibri
Dim colNum As Integer, lLastRow As Long
Dim dTotal As Double
Dim Targetcolumn As String

Targetcolumn = ActiveCell.Column
colNum = Targetcolumn
lLastRow =
Application.WorksheetFunction.CountA(Columns(Activ eCell.Column))
dTotal = Application.WorksheetFunction.Sum(Columns(ActiveCe ll.Column))
Cells(lLastRow + 1, ActiveCell.Column).Value = dTotal
End Sub

To try it, open Excel and in column A, insert a series of numbers in
cells A1:A5. Do the same in cells D1:D7. Click on cell A1 and run the
macro. Click on Cell D1 and run the macro..you'll get the point/idea.

Thanks, again!


On Mar 21, 3:15 pm, "Nick Hodge"
wrote:
Colibri

Not sure I totally understand but you could use a Worksheet_Selection
change
event,like below. (To invoke right click the sheet tab with the data and
select view code... and paste here). This will add a total to the last
entry
on a column that is clicked on providing the data in the column is
contiguous (no gaps)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim colNum As Integer, lLastRow As Long
Dim dTotal As Double
colNum = Target.Column
lLastRow = Application.WorksheetFunction.CountA(Columns(Targe t.Column))
dTotal = Application.WorksheetFunction.Sum(Columns(Target.C olumn))
Cells(lLastRow + 1, Target.Column).Value = dTotal
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
e.co.uk

wrote in message

oups.com...



OK, all, here's the scenario: I have a spreadsheet with 20 of columns.
Half of the columns have numbers in them. The columns are of different
lengths so some have 35 rows, some have 65 rows. I want to have a
macro whereby I can click on the first cell in the column (ie Colmn B)
that has a digit and the macro will add all the numbers in that column
and place the total at the next available cell at the bottom of the
column (ie in cell B37). Then, I want to move to a DIFFERENT column
(Column D) that has 65 numbers in it, run the macro and it will also
add up all the numbers in THAT column and place the total in the next
available cell (Cell D67) at the bottom of the column. And on and on
withthe rest of the columns with numbers. Does my question make sense
and is it possible?- Hide quoted text -

- Show quoted text -