Delete One Row From A 2D Variant Array

**Rick Rothstein** · 02-11-2016, 02:48 AM

Working in VBA with a 2D array in memory that was formed from a worksheet range is much faster than trying to work with the range directly on the worksheet itself. The way the array is formed is quite simple... just assign the range to a Variant variable. So, if you wanted to create an array in memory for range A1:M1000, you would (should) declare a Variant variable, for example...

Dim DataArr As Variant

and then assign the range to it...

DataArr = Range("A1:M1000")

Okay, with that brief introduction out of the way, I have seen on more than one occasion requests to be able to delete a single specified row from such a 2D Variant array. Finally, I decided to tackle the problem and see what I could come up with... this non-looping function is the result. The function accepts a 2-D Variant array and a row number for its arguments and it returns a 2-D Variant array with that row number removed.

Code:

Function DeleteArrayRow(Arr As Variant, RowToDelete As Long) As Variant
  Dim Rws As Long, Cols As String
  Rws = UBound(Arr) - LBound(Arr)
  Cols = "A:" & Split(Columns(UBound(Arr, 2) - LBound(Arr, 2) + 1).Address(, 0), ":")(0)
  DeleteArrayRow = Application.Index(Arr, Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(Arr) & ")"))))), Evaluate("COLUMN(" & Cols & ")"))
End Function

Note the last line of code is quite long. Here is a macro to test the function with that creates the 2-D Variant array from the range A1:AI1000 (1000 rows by 35 columns), which the macro will fill with data for you, and outputs the array (with row 35 removed) returned by the function to the range starting at cell AK1. Note that the test macro seeds the given range with some data for you so you don't have to do that on your own.

Code:

Sub Test()
  Dim Cell As Range, RemoveRow As Long, Data_Array As Variant, ArrLessOne As Variant
  
  ' Seed the range with some data
  For Each Cell In Range("A1:AI1000")
    Cell.Value = Cell.Address(0, 0)
  Next
  
  Data_Array = Range("A1:AI1000")
  RemoveRow = 35
  ArrLessOne = DeleteArrayRow(Data_Array, RemoveRow)
  Range("AK1").Resize(UBound(ArrLessOne, 1), UBound(ArrLessOne, 2)) = ArrLessOne
End Sub