Delete One Row From A 2D Variant Array
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
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Delete One Row From a ....... group of contiguous cells in a Spreadsheet
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..
Quote:
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
Use here of :.... arrOut() = Application.Index(arrIn(), rwsT(), clms())
So back to the codes given and discussed in this Thread, but again the use of
arrOut() = Application.Index(arrIn(), rwsT(), clms())
arrIn() we have ( as an Array by Rick or as a spreadsheet "Area" by snb )
All the "work" in the codes is to get the required rwsT() and clms() indices.
For no particular reason I am considering this as my Input "Area"
Using Excel 2007 32 bit
| Row\Col |
A |
B |
C |
D |
E |
F |
|---|
| 1 |
0 |
10 |
20 |
30 |
40 |
|
| 2 |
2 |
12 |
22 |
32 |
42 |
|
| 3 |
4 |
14 |
24 |
34 |
44 |
|
| 4 |
6 |
16 |
26 |
36 |
46 |
|
| 5 |
8 |
18 |
28 |
38 |
48 |
|
| 6 |
10 |
20 |
30 |
40 |
50 |
|
| 7 |
12 |
22 |
32 |
42 |
52 |
|
| 8 |
14 |
24 |
34 |
44 |
54 |
|
| 9 |
16 |
26 |
36 |
46 |
56 |
|
| 10 |
18 |
28 |
38 |
48 |
58 |
|
| 11 |
|
|
|
|
|
|
| Sheet: NPueyoGyanArraySlicing |
......
And to demo the Array returned by the Functions I will Paste it out here
Using Excel 2007 32 bit
| Row\Col |
M |
N |
O |
P |
Q |
|---|
| 16 |
|
|
|
|
|
| 17 |
0 |
10 |
20 |
30 |
40 |
| 18 |
2 |
12 |
22 |
32 |
42 |
| 19 |
4 |
14 |
24 |
34 |
44 |
| 20 |
6 |
16 |
26 |
36 |
46 |
| 21 |
10 |
20 |
30 |
40 |
50 |
| 22 |
12 |
22 |
32 |
42 |
52 |
| 23 |
14 |
24 |
34 |
44 |
54 |
| 24 |
16 |
26 |
36 |
46 |
56 |
| 25 |
18 |
28 |
38 |
48 |
58 |
| 26 |
|
|
|
|
|
| Sheet: NPueyoGyanArraySlicing |
So it follows that the main "work will be to get
rwsT() = { 1; 2; 3; 4; 6; 7; 8; 9; 10 } ' ( 2 Dimensional 1 "column" "Vertical" Array )
and
clms() = { 1, 2, 3, 4, 5 } ' ( 1 Dimension "pseudo Horizonal" Array )
_................................................. ...
