Handling blank cells while calculating SMA

Question

I'm trying to skip the blank cells from a range so that it does not alter my simple moving average. So I try to start from a range, "copy/paste" the non blank values within a new range called oRng so that I can use the new range to calculate my SMA.

I got a error 5 saying "Argument or procedure call incorrect" on this line

Set oRng = Application.Union(oRng, cell)

Here is my full code:

Function SMA(rng As Range, N As Integer)

    Dim oRng As Range
    Dim cell As Range

    On Error GoTo ErrHandler

    For Each cell In rng
        If not IsEmpty(cell) = True Then
            Set oRng = Application.Union(oRng, cell)
    Next cell

    SMA = Application.Average(oRng.Resize(N, 1))

ErrHandler:
    Debug.Print Err.Description
End Function

Answer 1

oRng.Resize(N, 1) won't work with a non-continous range. You can only resize continous ranges. Therfore you need to resize the cells before you collect them in oRng using Union.

Also after Dim oRng As Range the variable oRng is Nothing and you cannot use Nothing in Union so you need to test this first and directly Set oRng = cell.Resize(N, 1) the first time and then use Union for the others:

Public Function SMA(ByVal rng As Range, ByVal N As Long) As Variant
    Dim oRng As Range

    On Error GoTo ErrHandler

    Dim cell As Range
    For Each cell In rng
        If Not IsEmpty(cell) Then
            If oRng Is Nothing Then
                Set oRng = cell.Resize(N, 1)
            Else
                Set oRng = Application.Union(oRng, cell.Resize(N, 1))
            End If
    Next cell

    SMA = Application.Average(oRng)

ErrHandler:
    Debug.Print Err.Description
End Function