Total by Selection by Column, Help Please.

  • Thread starter Thread starter Bubu
  • Start date Start date
B

Bubu

Suppose i select a certain range, then a start a macro.
i want that macro to sum all the values for each column
included in my selection and to have the results just below.


suppose i have a situation like this :
A B C
----------------------------
1 3 9 7
2 5 1 8
3 1 5 3
3 2 1 1


I select range A1:C3, i start the macro, then i have the results like this ...

A B C
----------------------------
1 3 9 7
2 5 1 8
3 1 5 3
3 2 1 1
----------------------------
4 11 16 19

Important Note : range A1:C3 may be whatever, like F7:M26 or B9:F12,
for the case F7:M26 the results will be in F27:M27
for the case B9:F12 the results will be in B13:F13

Any Help ?

Best Regards.

Robert.
 
Hi Robert,

Here's one way to do it:

Sub AddSUMs()
Dim col As Range
Dim lTopRow As Long
Dim lRows As Long

If Selection.Areas.Count > 1 Then
MsgBox "Unable to process multiple areas."
Else
lTopRow = Selection(1, 1).Row
lRows = Selection.Rows.Count
For Each col In Selection.Columns
With Cells(lTopRow, col.Column)
.Offset(lRows).Formula = _
"=SUM(" & .Resize(lRows, 1).Address & ")"
End With
Next col
End If
End Sub

You may want to add some error handling, but this should work for you,
assuming you select a rectangular range. If not, you'd have to loop through
each column and find the cell below the last selected cell.

BTW, I looked at the code generated by the AutoSum button in Excel. I
thought there may be a corresponding VBA function, but there isn't. And the
generated code was pretty ugly. <g>

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
hi babu..may be this helps you.

Dim sumrow As Double

Sub aaa()
Sheets("Sheet1").Activate
For i = Selection.Column To Selection.Columns.Count + Selection.Column
- 1
For j = Selection.Row To Selection.Rows.Count + Selection.Row - 1
sumrow = sumrow + Cells(j, i)
Next j
Cells(Selection.Row + Selection.Rows.Count, i) = sumrow
sumrow = 0
Next i
End Sub
 
Back
Top