Hi,
Here's a couple of methods, but the first requires you to have the active
cell in the range that you are using, and also it will require you not to
have a contiguous range (the CurrentRegion identifies all contiguous cells
that are not blank into one rectangular block, so any rows or columns within
that block that are completely empty will end the range).
The second identifies the last cell in the chosen column, and uses a
predefined first row (both methods have a predefined column).
Public Sub MsgAndSum()
Const intColumnNumber As Integer = 2
Const lngFirstRow As Long = 1
'METHOD 1
MsgBox (Cells(ActiveCell.CurrentRegion.Rows.Count +
ActiveCell.CurrentRegion.Row - 1, _
intColumnNumber).Value)
Cells(ActiveCell.CurrentRegion.Rows.Count +
ActiveCell.CurrentRegion.Row, intColumnNumber).Value _
=
Application.WorksheetFunction.Sum(Range(Cells(ActiveCell.CurrentRegion.Row, _
intColumnNumber), Cells(ActiveCell.CurrentRegion.Rows.Count
+ _
ActiveCell.CurrentRegion.Row - 1, intColumnNumber)))
'METHOD 2
MsgBox (Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp))
Cells(Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp).Row + 1,
intColumnNumber).Value = _
Application.WorksheetFunction.Sum(Range(Cells(lngFirstRow,
intColumnNumber), _
Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp)))
End Sub
Both methods have drawbacks, 1 requires the activecell to be in the range
and all the data to be contiguous. But if you have more data below the range
then it is better as Method 2 will look at all the data in the column even if
non-contiguous.
There will be many more methods, the best will depend on knowing how your
data is formed.
Cheers,
Sean.