Select cell after last row in selection?

[Gustaf]

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

 

[Madiya]

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

 

[Gustaf]

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

 

[Gustaf]

I found the solution I wanted now:

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

Gustaf

 

[Gord Dibben]

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

 

[Rick Rothstein \(MVP - VB\)]

I found the solution I wanted now:

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