Delete One Row From A 2D Variant Array

[snb]

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

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]

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..

... 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