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

**Admin** · 02-11-2016, 08:54 AM

**Rick Rothstein** · 02-11-2016, 09:37 AM

Originally Posted by Admin

Nice one Rick. Though, I don't see any performance gain over looping the array.

I had assumed that avoiding the nested loop would be faster, but you are right... I just set up a test and they both run in approximately the same time... actually, the nested loop might even be a hair faster (it appeared to run about 0.005 seconds faster, on average, given my limited testing). For those wondering about the code for the nested loop version, this is what I used...

Code:

Sub Test2()
  Dim R As Long, C As Long, Idx As Long, 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
  ReDim ArrLessOne(1 To UBound(Data_Array, 1) - 1, 1 To UBound(Data_Array, 2))
  For R = 1 To UBound(Data_Array)
    If R <> RemoveRow Then
      Idx = Idx + 1
      For C = 1 To UBound(Data_Array, 2)
        ArrLessOne(Idx, C) = Data_Array(R, C)
      Next
    End If
  Next
  Range("AK1").Resize(UBound(ArrLessOne, 1), UBound(ArrLessOne, 2)) = ArrLessOne
End Sub

**BigFastPanda** · 02-23-2020, 03:10 PM

Originally Posted by Rick Rothstein

I had assumed that avoiding the nested loop would be faster, but you are right... I just set up a test and they both run in approximately the same time... actually, the nested loop might even be a hair faster (it appeared to run about 0.005 seconds faster, on average, given my limited testing). For those wondering about the code for the nested loop version, this is what I used...

Code:

Sub Test2()
  Dim R As Long, C As Long, Idx As Long, 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
  ReDim ArrLessOne(1 To UBound(Data_Array, 1) - 1, 1 To UBound(Data_Array, 2))
  For R = 1 To UBound(Data_Array)
    If R <> RemoveRow Then
      Idx = Idx + 1
      For C = 1 To UBound(Data_Array, 2)
        ArrLessOne(Idx, C) = Data_Array(R, C)
      Next
    End If
  Next
  Range("AK1").Resize(UBound(ArrLessOne, 1), UBound(ArrLessOne, 2)) = ArrLessOne
End Sub

This just the solution I'm looking for , thank you Rick!

**Admin** · 02-11-2016, 09:52 AM

I think you can remove the second

Code:

If R <> RemoveRow Then

**Rick Rothstein** · 02-11-2016, 10:43 AM

Originally Posted by Admin

I think you can remove the second

Code:

If R <> RemoveRow Then

from the code.

Fixed... thanks for the head's up (damn last minute edits.

**DocAElstein** · 02-12-2016, 03:43 AM

**snb** · 04-04-2016, 01:47 PM

If you want to remove row 12 from array [A1:K20]

Code:

Sub M_snb()
  sp = F_snb([a1:K20], 12)
  Cells(1, 16).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

Function F_snb(sn, y)
  sn.Name = "snb_002"
  F_snb = Application.Index(sn, Application.Transpose(Split(Trim(Replace(" " & Join([transpose(row(snb_002))]) & " ", " " & y & " ", " ")))), [column(snb_002)])
End Function

For more see: http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.10

**DocAElstein** · 06-08-2016, 04:00 AM

Delete One Row From a ....... group of contiguous cells in a Spreadsheet

'Coments on snb and Rick codes and further solutuons......

Hi,
I have learned a lot in the past from opening up these "One liner" codes from the likes of Rick and snb. These were no exceptions. I did some notes for my own use. I thought I would share them, in case any novices hitting the Thread might find them useful... I will try to keep it as brief as possible but there is a wealth of knowledge "hidden" in there.!!

Very briefly first:

Rick's code:
Rick is working on an Array, what the Thread is about. ( He just happens to make that Array, ( arrIn() = DataArr or Arr ) in a typical way form a Spreadsheet Range "capture" through the .Value Property.
This .Value Property when applied to a Range for more than one cell returns a Field of Elements of Variant types. These can be assigned directly to a dynamic Array of variant Elements.
( That was Ricks comment more or less right at the start of the Thread. )
_ That Array is sent to the Function which returns the Modified Array. It has to be a Variant Element type as the used .Index method returns a Field on Variant Element Types ( for all but the one 0 argument slicing case ....._
https://usefulgyaan.wordpress.com/20...ication-index/
...._)

(_.......
In fact, both codes are similar and are basically using this sort of code line ( what I often refer to as a "Magic" or "neat Code line )

arrOut() = Application.Index(arrIn(), rwsT(), clms())

_....)

snb's Code
snb is working directly on the Range, ( rngIn = sn ) , - This..

Originally Posted by snb

... from array [A1:K20].....

...maybe is not quite right..... a "cheat"..****..but a lot more about that later
What he returns from

arrOut() = Application.Index( sn , rwsT(), clms())

is also an Array as .Index method returns a Field on Values of Variant Element Types ( for all but the one 0 argument slicing case ) . I will call this a "cheat" just as a convenient reference to the "way" he does it, not to be taken as literally, sort of, as actually after thinking about it, I decided to do something similar in my alternatives ****, which I present later here in this thread......
(_.... Edit: In fact , I found this "Cheat" and the associated use of a Name for that imputed Range fascinating and got quite side tracked with it !!
http://www.excelforum.com/excel-prog...acket-for.html _.......)

_.......
So...
My codes look massive compared to the original. Amongst other things I declare a lot of vba Variables.( And I use Option Explicit, to force me to do that ) In the final simplified codes the actual values set by the Variables are substituted into where the variables are used. So the need to declare them is gone_..............
_.................... You end up then with the final code lines or code line.

When I have finished I will give my attempt at an alternative ****.

_.....I "farmed out" my "opened up" codes here, ( to save cluttering up this thread ! )

Snb Code:
http://www.excelfox.com/forum/showth...ted=1#post9826

Rick code:
http://www.excelfox.com/forum/showth...=9824#post9824

_ If anyone is interested in my explanation then it might be worth copying the ranges shown below to a spare Worksheet, copying the codes to a spare Code Module and then following it through in Debug ( F8 ) mode as you work through my explanations.

_I tried to write the explanations and the opened up codes such that both codes and explanations run as much as possible in parallel

**DocAElstein** · 06-08-2016, 04:01 AM

Code Explanations, or rather here,_....
_........A quick refresh / explanation of the "neat", "Magic" code line in General

I 'comment at the Function starts along the lines:
10