I'm writing a macro to sum all values in a selection (constrained to 1
column), except numbers that are displayed in gray. The macro should
work exactly like the built-in Sum button, so that the sum is placed
below the last row in the selection, and it's that part I can't figure
out how to do. How do I select the cell after the last row in the selection?

Public Sub CalculateSum()

' Prepare a sum variable
nsum = 0

' Make sure there is only one column in the range
If Selection.Columns.count 1 Then
MsgBox "This macro can only be used on 1 column at a time."
Exit Sub
End If

' Loop through selected range
For Each c In Selection.Cells
If c.Font.ColorIndex < 48 Then
nsum = nsum + c.Value
End If
Next c

MsgBox nsum

End Sub

Many thanks,

Gustaf

On Jun 28, 4:50*pm, Gustaf wrote:
I'm writing a macro to sum all values in a selection (constrained to 1
column), except numbers that are displayed in gray. The macro should
work exactly like the built-in Sum button, so that the sum is placed
below the last row in the selection, and it's that part I can't figure
out how to do. How do I select the cell after the last row in the selection?

Public Sub CalculateSum()

* * *' Prepare a sum variable
* * *nsum = 0

* * *' Make sure there is only one column in the range
* * *If Selection.Columns.count 1 Then
* * * * *MsgBox "This macro can only be used on 1 column at a time."
* * * * *Exit Sub
* * *End If

* * *' Loop through selected range
* * *For Each c In Selection.Cells
* * * * *If c.Font.ColorIndex < 48 Then
* * * * * * *nsum = nsum + c.Value
* * * * *End If
* * *Next c

* * *MsgBox nsum

End Sub

Many thanks,

Gustaf

Just add this line at the end of the code.
Range("A65536").End(xlUp).Offset(1, 0).Value = nsum

Regards,
Madiya

Madiya wrote:

Just add this line at the end of the code.
Range("A65536").End(xlUp).Offset(1, 0).Value = nsum

Thank you, but this is not really what I want. This adds the value to
the first column, after the last row. I want the value in the same
column as the range, but just below the range.

Gustaf

I found the solution I wanted now:

Selection.End(xlDown).Offset(1, 0).Value = nsum

Gustaf

Which will place nsum in the first blank cell in the selected column.

OK as long as your data is contiguous with no blanks.


Gord Dibben MS Excel MVP

On Sat, 28 Jun 2008 17:48:37 +0200, Gustaf wrote:

I found the solution I wanted now:

Selection.End(xlDown).Offset(1, 0).Value = nsum

Gustaf

I found the solution I wanted now:

Selection.End(xlDown).Offset(1, 0).Value = nsum

OK as long as your data is contiguous with no blanks.

Good point! This should work for all situations...

Cells(Selection.Row + Selection.Count, Selection.Column).Value = nsum

Rick