VBA Summing a Column of Variable Length

Question

Another VBA question (I'm on fire lately) As the title says, I am trying to sum a column that can can be of a variable length and then stick that sum in cell F3, but I am running into a an "application or object defined error.

Here's my code:

Dim last As Range, sum As Variant

    ActiveSheet.Range("M8").Select
    Set last = Selection.End(xlDown)
    With Worksheets("Data")
        sum = WorksheetFunction.sum(.Range("M8:M" & last))
    End With

    Range("F:3") = sum

Answer 1

Use this function to robustly count the non-empty cells down from a cell.

' Enumerate non-empty cells down the rows.
Public Function CountRows(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountRows = 0
    ElseIf IsEmpty(r.Offset(1, 0)) Then
        CountRows = 1
    Else
        CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.Count
    End If
End Function

Answer 2

Using your method, last needs to be a Long to which you assign the row number.

Dim last As Long
Dim sum As Long

ActiveSheet.Range("M8").Select
last = Selection.End(xlDown).Row
With Worksheets("Data")
  sum = WorksheetFunction.sum(.Range("M8:M" & last))
End With

Range("F3") = sum

You could also do it a little more efficiently, by using

last = ActiveSheet.Range("M8").End(xlDown).Row

and not using the Select.

Answer 3

There is a non VBA way. In cell F3 type the following:

=SUM(OFFSET($M$8,0,0,COUNTA(M:M),1))

NB - this assumes the only content of column M are the numbers you'd like to sum

Answer 4

With Worksheets("Data")
    .Range("F3").Value = Application.Sum(.Range(.Range("M8"), .Range("M8").End(xlDown))
End With