DROP Excel 365 function compatible for versions of excel 2007 and newer

[johnnyL]

I have decided to start releasing code that should be backward compatible to excel 2007.

The following UDF should be able to be added to any version of Excel 2007 or newer version of excel and perform very similar to the excel 365 function DROP

Function DROP(array_range As Range, Optional rows As Long = 0, Optional cols As Long = 0) As Variant                ' Excel 365
'
    Dim ColumnsInRange      As Long, RowsInRange        As Long
    Dim ArrayColumn         As Long, ArrayRow           As Long
    Dim OutputArrayColumns  As Long, OutputArrayRows    As Long
    Dim RangeColumn         As Long, RangeRow           As Long
    Dim EndColumn           As Long, EndRow             As Long
    Dim OutputArray         As Variant
'
    RowsInRange = array_range.rows.count                                                                            ' Get the # of rows in the range
    ColumnsInRange = array_range.columns.count                                                                      ' Get the # of columns in the range
'
    If Abs(rows) >= RowsInRange Or Abs(cols) >= ColumnsInRange Then                                                 ' If there are more rows or columns specified than what exists then ...
        DROP = "#CALC!"                                                                                             '   Create the error message to return
        Exit Function                                                                                               '   Exit the function
    End If
'
    If cols < 0 Then EndColumn = ColumnsInRange + cols                                                              ' If cols < 0 then set the EndColumn
    If rows < 0 Then EndRow = RowsInRange + rows                                                                    ' If rows < 0 then set the EndRow
'
    OutputArrayRows = RowsInRange                                                                                   ' Initially set OutputArrayRows to the maximum size
    If Abs(rows) > 0 Then OutputArrayRows = RowsInRange - Abs(rows)                                                 ' Adjust the OutputArrayRows smaller if required
'
    OutputArrayColumns = ColumnsInRange                                                                             ' Initially set OutputArrayColumns to the maximum size
    If Abs(cols) > 0 Then OutputArrayColumns = ColumnsInRange - Abs(cols)                                           ' Adjust the OutputArrayColumns smaller if required
'
    ReDim OutputArray(1 To OutputArrayRows, 1 To OutputArrayColumns)                                                ' Set the row & column size of OutputArray
'
    If rows >= 0 Then                                                                                               ' If rows > 0 then ...
        If cols >= 0 Then                                                                                           '   If cols > 0 then ...
'
' Handle case where rows & cols are >= zero
            For RangeRow = rows + 1 To RowsInRange                                                                  '       Loop through the needed rows of the range
                ArrayRow = ArrayRow + 1                                                                             '           Increment ArrayRow
'
                For RangeColumn = cols + 1 To ColumnsInRange                                                        '           Loop through the needed columns of the range
                    ArrayColumn = ArrayColumn + 1                                                                   '               Increment ArrayColumn
                    OutputArray(ArrayRow, ArrayColumn) = array_range.Cells(RangeRow, RangeColumn).value             '               Save the value from range to OutputArray
                Next                                                                                                '           Loop back
               
                ArrayColumn = 0                                                                                     '           Reset ArrayColumn
            Next                                                                                                    '       Loop back
        Else                                                                                                        '   Else ...
'
' Handle case where rows are >= zero & cols are < zero
            For RangeRow = rows + 1 To RowsInRange                                                                  '       Loop through the needed rows of the range
                ArrayRow = ArrayRow + 1                                                                             '           Increment ArrayRow
'
                For RangeColumn = 1 To EndColumn                                                                    '           Loop through the needed columns of the range
                    ArrayColumn = ArrayColumn + 1                                                                   '               Increment ArrayColumn
                    OutputArray(ArrayRow, ArrayColumn) = array_range.Cells(RangeRow, RangeColumn).value             '               Save the value from range to OutputArray
                Next                                                                                                '           Loop back
'
                ArrayColumn = 0                                                                                     '           Reset ArrayColumn
            Next                                                                                                    '       Loop back
        End If
    Else                                                                                                            ' Else ...
'
' Handle case where rows are < zero & cols are >= zero
        If cols >= 0 Then                                                                                           '   If cols >= 0 then ...
            For RangeRow = 1 To EndRow                                                                              '       Loop through the needed rows of the range
                ArrayRow = ArrayRow + 1                                                                             '           Increment ArrayRow
'
                For RangeColumn = cols + 1 To ColumnsInRange                                                        '           Loop through the needed columns of the range
                    ArrayColumn = ArrayColumn + 1                                                                   '               Increment ArrayColumn
                    OutputArray(ArrayRow, ArrayColumn) = array_range.Cells(RangeRow, RangeColumn).value             '               Save the value from range to OutputArray
                Next                                                                                                '           Loop back
'
                ArrayColumn = 0                                                                                     '           Reset ArrayColumn
            Next                                                                                                    '       Loop back
        Else                                                                                                        '   Else ...
'
' Handle case where rows are < zero & cols are < zero
            For RangeRow = 1 To EndRow                                                                              '       Loop through the needed rows of the range
                ArrayRow = ArrayRow + 1                                                                             '           Increment ArrayRow
'
                For RangeColumn = 1 To EndColumn                                                                    '           Loop through the needed columns of the range
                    ArrayColumn = ArrayColumn + 1                                                                   '               Increment ArrayColumn
                    OutputArray(ArrayRow, ArrayColumn) = array_range.Cells(RangeRow, RangeColumn).value             '               Save the value from range to OutputArray
                Next                                                                                                '           Loop back
               
                ArrayColumn = 0                                                                                     '           Reset ArrayColumn
            Next                                                                                                    '       Loop back
        End If
    End If
'
    DROP = OutputArray                                                                                              '
End Function

Let me know your positive or negative results, please give examples of what you tested when posting your results so we can make any corrections that I am sure will need to be made.