Function EE_RangeChange(RangeToChange As range, StartRowOffset As Integer, StartColOffset As Integer, _
    EndRowOffset As Integer, EndColOffset As Integer, Optional MaxRows As Long, Optional MaxCols As Long) As range
' creates a new range based on resizing the existing range
' so If we add StartRowOffset =1, we move the startrow down by 1
' does not error if we go off the sheet, uses sheet max dimensions
'http://excelexperts.com/xla-routines-eeRangeChange    for updates on this function

    Dim CurrentStartRow As Long
    Dim CurrentEndRow As Long
    Dim CurrentStartCol As Long
    Dim CurrentEndCol As Long
    Dim NewStartRow As Long
    Dim NewEndRow As Long
    Dim NewStartCol As Long
    Dim NewEndCol As Long
    Dim Sht As Worksheet
 
    If RangeToChange.Areas.Count > 1 Then
        Set EE_RangeChange = RangeToChange
        Exit Function
    End If
 
    CurrentStartRow = RangeToChange.Row
    CurrentEndRow = CurrentStartRow + RangeToChange.Rows.Count - 1
    CurrentStartCol = RangeToChange.Column
    CurrentEndCol = CurrentStartCol + RangeToChange.Columns.Count - 1
 
    NewStartRow = CurrentStartRow + StartRowOffset
    NewEndRow = CurrentEndRow + EndRowOffset
    NewStartCol = CurrentStartCol + StartColOffset
    NewEndCol = CurrentEndCol + EndColOffset
 
    NewStartRow = Application.Min(NewStartRow, Rows.Count)
    NewStartRow = Application.Max(NewStartRow, 1)
 
    NewEndRow = Application.Min(NewEndRow, Rows.Count)
    NewEndRow = Application.Max(NewEndRow, 1)
 
    If MaxRows <> 0 Then
        If NewEndRow - NewStartRow > MaxRows Then
            NewEndRow = NewStartRow + MaxRows - 1
        End If
    End If
 
    NewStartCol = Application.Min(NewStartCol, Columns.Count)
    NewStartCol = Application.Max(NewStartCol, 1)
 
    NewEndCol = Application.Min(NewEndCol, Columns.Count)
    NewEndCol = Application.Max(NewEndCol, 1)
 
    If MaxCols <> 0 Then
        If NewEndCol - NewStartCol > MaxCols Then
            NewEndCol = NewStartCol + MaxCols - 1
        End If
    End If
 
    Set Sht = RangeToChange.Parent
 
    Set EE_RangeChange = range(Sht.Cells(NewStartRow, NewStartCol), Sht.Cells(NewEndRow, NewEndCol))
 
End Function