XLA routines: EE_RangeTrim

Nick's picture
Use EE_RangeTrim to trim a range that is passed to date functions to make the calc quicker
Function EE_RangeTrim(rng As range) As range
'Returns a new range containing only the populated cells
'- use RangeTrim for all the date functions to prevent looping through the whole range.

    Dim rngC As range
'http://excelexperts.com/xla-routines-eeRangeTrim    for updates on this function

    If rng.Cells.Count = 1 Then
        If rng.value <> vbNullString Then
            Set EE_RangeTrim = rng
            Exit Function
        End If
    End If
    On Error Resume Next
    Set rngC = rng.SpecialCells(xlCellTypeFormulas)
    If Not rngC Is Nothing Then
        Set rngC = Union(rngC, rng.SpecialCells(xlCellTypeConstants))
        Set rngC = rng.SpecialCells(xlCellTypeConstants)
    End If
    Err.Clear: On Error GoTo 0: On Error GoTo -1
    Set EE_RangeTrim = rngC
End Function