Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for column sum
Hi all
I need a macro to add a column its sum, right below the end of the column. Column length (i.e. # of rows) is variable. For instance, let's say data is in D1:D50, I want the column total in cell D51. If the data was in D1:D15, I would want the sum in D16. Thanks for help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for column sum
Sub SelectFirstToLastInColumn()
Dim TopCell, BottomCell, x Set TopCell = Cells(1, ActiveCell.Column) Set BottomCell = Cells(16384, ActiveCell.Column) If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown) If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp) If TopCell.Row = 16384 And BottomCell.Row = 1 Then ActiveCell.Select Else Range(TopCell, BottomCell).Select x = Selection.Address Cells(BottomCell.Row + 1, ActiveCell.Column).Select ActiveCell.Formula = "=sum(" & x & ")" End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for column sum
I dont understand where you close the If..Then blocks.
It doesn't work. thanks for help "excelent" wrote in message ... Sub SelectFirstToLastInColumn() Dim TopCell, BottomCell, x Set TopCell = Cells(1, ActiveCell.Column) Set BottomCell = Cells(16384, ActiveCell.Column) If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown) If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp) If TopCell.Row = 16384 And BottomCell.Row = 1 Then ActiveCell.Select Else Range(TopCell, BottomCell).Select x = Selection.Address Cells(BottomCell.Row + 1, ActiveCell.Column).Select ActiveCell.Formula = "=sum(" & x & ")" End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for column sum
hope this help:
Sub SelectFirstToLastInColumn() ' line 1 Dim Top, Bot, x ' line 2 Set Top = Cells(1, ActiveCell.Column) ' line 3 Set Bot = Cells(16384, ActiveCell.Column) ' line 4 If IsEmpty(Top) Then Set Top = Top.End(xlDown) ' line 5 If IsEmpty(Bot) Then Set Bot = Bot.End(xlUp) ' line 6 If Top.Row = 16384 And Bot.Row = 1 Then ActiveCell.Select Else Range(Top, Bot).Select ' line 7 x = Selection.Address ' line 8 Cells(Bot.Row + 1, ActiveCell.Column).Select ' line 9 ActiveCell.Formula = "=sum(" & x & ")" ' line 10 End Sub ' line 11 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro for column sum
remember select column then run macro
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |