Delete item at specific position of index in array

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Delete item at specific position of index in array

Post by YasserKhalil »

Hello everyone
I am trying to delete an item at specific position in 1D array and here's my try

Code: Select all

Sub Test()
    Dim arr
    arr = Array(1, 2, 3, 4, 5)
    DeleteItem arr, 0
    Debug.Print Join(arr, ", ")
End Sub

Sub DeleteItem(ByRef arr, v)
    Dim rv(), i As Long, n As Long
    ReDim rv(LBound(arr) To UBound(arr))
    For i = LBound(arr) To UBound(arr)
        If arr(i) <> arr(v) Then rv(i - n) = arr(i) Else n = n + 1
    Next i
    If (UBound(arr) - n) >= LBound(arr) Then ReDim Preserve rv(LBound(arr) To UBound(arr) - n)
    arr = rv
End Sub
The code is working but is there a more compact way or easier idea for that task?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete item at specific position of index in array

Post by HansV »

You could use

Code: Select all

Sub DeleteItem(ByRef arr, v)
    arr = Filter(arr, arr(v), False)
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Delete item at specific position of index in array

Post by YasserKhalil »

Very elegant solution but this removes all the values that exist inside the array

Code: Select all

Sub Test()
    Dim arr
    arr = Array(1, 2, 3, 1, 4, 5)
    DeleteItem arr, 0
    Debug.Print Join(arr, ", ")
End Sub
The target is to remove the item related to a specific index.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete item at specific position of index in array

Post by HansV »

Then it'll be more or less like your code. Small variations:

Code: Select all

Sub DeleteItem(arr, v As Long)
    Dim i As Long
    If v < LBound(arr) Or v > UBound(arr) Then
        Exit Sub
    End If
    ReDim rv(LBound(arr) To UBound(arr) - 1)
    For i = LBound(arr) To v - 1
        rv(i) = arr(i)
    Next i
    For i = v + 1 To UBound(arr)
        rv(i - 1) = arr(i)
    Next i
    arr = rv
End Sub
and

Code: Select all

Sub DeleteItem(arr, v As Long)
    Dim i As Long
    Dim n As Long
    If v < LBound(arr) Or v > UBound(arr) Then
        Exit Sub
    End If
    ReDim rv(LBound(arr) To UBound(arr) - 1)
    n = LBound(arr)
    For i = LBound(arr) To UBound(arr)
        If i <> v Then
            rv(n) = arr(i)
            n = n + 1
        End If
    Next i
    arr = rv
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Delete item at specific position of index in array

Post by YasserKhalil »

Thanks a lot my tutor.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Delete item at specific position of index in array

Post by Doc.AElstein »

Hi
Another way..
A solution without looping.

Because you are wanting something done to a 1D array, we can do a bit of fiddling around with Join and Split
We can take your array ,
{1, 2, 3, 4, 5}
And turn it easily enough into something of this form
"|1|2|3|4|5|"

We have a worksheet function, Application.WorksheetFunction.Substitute , which would let us get at specific ones of those separators "|"
After that its just a bit of tedious string manipulation.

Here is a first attempt Full version and “one liner” versions. ( macros also in uploaded text file )
It’s a bit messy, and I expect it can all be shortened a bit and tidied up. Some time later I might do a better version and post again. But that attempt demonstrates the basic idea.
I expect I personally might prefer to stick with a looping version, but I thought it was interesting to post out of interest for comparison…

Alan

P.S. In all my macros, Indx is the item number of the element , ( starting at 0 ) , - It is not the actual value .
So , in the examples, choosing indx = 0 would result in the array returned being {"2", "3", "4", "5"} , because the first item position of index in array element is removed

Here is a function version of those referenced macros: ( In the test example, the returned array would be {"1", "3", "4", "5"} because the second element, item index 1 , is removed )

Code: Select all

 Sub testFunction()
Dim arr1D() As Variant:  Let arr1D() = Array(1, "2", 3, 4, 5)              '             the returned array will be {"1", "3", "4", "5"} because the second element, item index 1 , will be  removed
 Let arr1D() = DeleteItem(arr1D(), 1)
End Sub

Function DeleteItem(ByVal Var As Variant,  ByVal Indx As Long) As Variant
 Let DeleteItem = Application.Index(Split(Mid(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "§", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "§", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "§", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "§", Indx + 2), "|", "@", Indx + 1), "§") + 1), 2, Len(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "§", Indx + 2), "|", "@", Indx + 1), _
 InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "§", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "§", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(Var, "|") & "|", "|", "§", Indx + 2), "|", "@", Indx + 1), "§") + 1)) - 2), "|"), Evaluate("=Column(A:" & Split(Cells(1, UBound(Var)).Address, "$", -1, vbBinaryCompare)(1) & ")/Column(A:" & Split(Cells(1, UBound(Var)).Address, "$", -1, vbBinaryCompare)(1) & ")"), Evaluate("=Column(A:" & Split(Cells(1, UBound(Var)).Address, "$", -1, vbBinaryCompare)(1) & ")")) '
End Function
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 16 Jan 2021, 07:36, edited 3 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Delete item at specific position of index in array

Post by YasserKhalil »

That's great Mr. Alan.
But a little note, the numbers now inside the array (the array elements) are strings not integers. Is there a way to fix that point?

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Delete item at specific position of index in array

Post by Doc.AElstein »

YasserKhalil wrote:
15 Jan 2021, 19:31
the numbers now inside the array (the array elements) are strings not integers. Is there a way to fix that point?
I do not think that is possible using this way. I think we are limited fundamentally in this way by the Split( ) function, which always returns string types.
( In most things in VBA coding, when a number is expected, a string which looks like a number would usually be accepted without any problem.
( I only know of one exception , but there may be others. – This only exception that I know about is when referencing worksheets:
If I use Worksheets.Item("1") or If I use Worksheets("1") , then any coding using this will try to reference a worksheet that has the tab Name of 1
It does not matter in which tab order that worksheet is. For example this , http://i.imgur.com/HialA5M.jpg , is referenced with Worksheets.Item("1") or Worksheets("1")
On the other hand, the following will always reference the first tab, regardless of what the worksheet tab Name is
Worksheets.Item(1) or Worksheets(1)
In that last example this would be Worksheets.Item(1) or Worksheets(1)
http://i.imgur.com/8Rs3CXA.jpg
) )
Last edited by Doc.AElstein on 16 Jan 2021, 10:39, edited 3 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Delete item at specific position of index in array

Post by YasserKhalil »

Thank you very much Mr. Alan
Best Regards to all of you.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Delete item at specific position of index in array

Post by Doc.AElstein »

Doc.AElstein wrote:
15 Jan 2021, 18:18
......I expect it can all be shortened a bit and tidied up. Some time later I might do a better version and post again. .....
This looks a bit better…

Code: Select all

 Sub DeleteItemByIndexIn1DArraySHG2() '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35980&p=279809#p279809    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15218&viewfull=1#post15218
Dim Indx As Long '
 Let Indx = 1 '  1 is for deleting the first element
Dim arr1D() As Variant: Let arr1D() = Array(1, 2, 3, 4, 5)

 Let arr1D() = Application.Index(Split(Mid(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))"), 2, Len(Evaluate("=Left(""," & Join(arr1D(), ",") & """, Find(""|"", Substitute(""," & Join(arr1D(), ",") & """, "","", ""|"", " & Indx & "))-1)&"",""&Right(""," & Join(arr1D(), ",") & ","", Len(""" & Join(arr1D(), ",") & """) - (Find(""|"", Substitute(""," & Join(arr1D(), ",") & ","", "","", ""|"", " & Indx + 1 & "))-2))")) - 2), ","), 1, 0) '  Full workings: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15218&viewfull=1#post15218
End Sub 
( In this example, Indx, is the Index number of the element that we want to remove starting from 1 – For the first element Indx must be given as 1

In Words , this is how the main code line works…
My 1 D array , for example , {1,2,3,4,5} , is turned into a single text string, “1,2,3,4,5”. ( The separating thing, a comma in this case, is arbitrary. You should choose some character that is not likely to appear in any of your data.)
The next thing to do is add additional leading and trailing separating things ( commas in this example ) , so in the example it would then look like “,1,2,3,4,5,”
Substitute is used to change the comma before the element you want to some other arbitrary separating thing.
So lets say we used a | and are wanting the first element ( Indx=1 ) removed. We then would have like
“|1,2,3,4,5,”
We then do a Find to get the position of that |
Substitute is used again to change the comma after the element you want to some other arbitrary separating thing.
So lets say we used a | again. ( we are still wanting the first element) We then would have like
“,1|2,3,4,5,”
We then do a Find to get the position of that |
So we now know where the start and the end is of the element that we want to remove are
We can use this information to determine the string before, and to determine the string after, the element that we want to remove.
So we put those two strings together and that gives us the original string without the element that we want to remove.
Finally we Split that text back into an array

( Once again we will have all string elements out, regardless of what element types we have in our original array )

Alan
_.______
Here is the full workings in the development of the code line
https://excelfox.com/forum/showthread.p ... #post15218
( also it’s the last macro in the uploaded text file.. )
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Delete item at specific position of index in array

Post by YasserKhalil »

That's great. Thank you very much Mr. Alan

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Delete item at specific position of index in array

Post by rory »

Remember that Evaluate is limited to 255 characters, so this will only work with small arrays with limited characters/digits. I also doubt it's as efficient as simply looping. It's certainly a lot less intelligible.
Regards,
Rory

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Delete item at specific position of index in array

Post by Doc.AElstein »

rory wrote:
19 Jan 2021, 11:53
Remember that Evaluate is limited to 255 characters, so this will only work with small arrays.
That’s a good point.
In fact, while I was developing this solution, I hit exactly that problem at the last step of like, pseudo …_
Mid(JointedJoint, 2, Len(JointedJoint) - 2)
_....my final single spreadsheet formula string was this

Code: Select all

 =Mid(Left(",1,2,3,4,5", Find("|", Substitute(",1,2,3,4,5", ",", "|", 1))-1)&","&Right(",1,2,3,4,5,", Len("1,2,3,4,5") - (Find("|", Substitute(",1,2,3,4,5,", ",", "|", 2))-2)),2,Len(Left(",1,2,3,4,5", Find("|", Substitute(",1,2,3,4,5", ",", "|", 1))-1)&","&Right(",1,2,3,4,5,", Len("1,2,3,4,5") - (Find("|", Substitute(",1,2,3,4,5,", ",", "|", 2))-2)))) 
That came out at 352 characters, so I had to do the last bit, the Mid( ) bit, in VBA, pseudo like…_
Mid(EvaluateBit, 2, Len(EvaluateBit) - 2)

With some coding things, I think a horizontal limit is sometimes sensible, but the limit in the Evaluate(“ “) string is hard to understand why, since I think strings , strings in cells, formulas, have much higher limits.
It seems a shame to limit the possibilities of such beautiful code lines.
_.__________________
rory wrote:
19 Jan 2021, 11:53
I also doubt it's as efficient
At a guess I would think that might be correct, but without extensive tests I thinks its hard to say. A simple evaluate alternative to looping often knocks the socks off the looping speed, but with more complicated evaluate one liner alternatives, similar to this one, I have seen it go both ways.
_._______________

( My previous offering was mostly not using Evaluate. In fact using one of my solutions in the referenced extended workings and solutions , it can do without it completely

Code: Select all

 Sub Test2FOFFEvaluate()
Dim Indx As Long
 Let Indx = 4

Dim arr1D() As Variant:  Let arr1D() = Array(1, 2, 3, 4, 5)
 Let arr1D() = Application.Index(Split(Mid(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1), 2, Len(Left$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), _
 InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Application.WorksheetFunction.Substitute(Application.WorksheetFunction.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1)) - 2), "|"), 1, 0) '

End Sub
Its beauty is slightly tarred by it not fitting on a code window line, although it is a beautiful single code line alternative to looping , none the less.
_._____
Last edited by Doc.AElstein on 19 Jan 2021, 14:45, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Delete item at specific position of index in array

Post by Doc.AElstein »

In fact, coming back to this, I just realised something I missed… this will work also _...

Code: Select all

 Sub Test2FOFFEvaluate2()
Dim Indx As Long
 Let Indx = 4
Dim Ob As Object: Set Ob = Application.WorksheetFunction
Dim arr1D() As Variant:  Let arr1D() = Array(1, 2, 3, 4, 5)
 Let arr1D() = Application.Index(Split(Mid(Left$(Ob.Substitute(Ob.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Ob.Substitute(Ob.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Ob.Substitute(Ob.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Ob.Substitute(Ob.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1), 2, Len(Left$(Ob.Substitute(Ob.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Ob.Substitute(Ob.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(Ob.Substitute(Ob.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(Ob.Substitute(Ob.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1)) - 2), "|"), 1, 0) '
End Sub 
That’s rather nice , since we now have another very beautiful single code line.

That idea lends itself also to do it With this alternative…

Code: Select all

 Sub Test2FOFFEvaluate3()
Dim Indx As Long
 Let Indx = 4
Dim arr1D() As Variant:  Let arr1D() = Array(1, 2, 3, 4, 5)
    With Application.WorksheetFunction
     Let arr1D() = Application.Index(Split(Mid(Left$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1), 2, Len(Left$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1)) - 2), "|"), 1, 0) '
    End With
End Sub
That’s quite beautiful also. Once again it may not be too efficient, but as the saying goes , who cares about efficiency and intelligiblism if you want to be beautiful :) - I may stumble on another way to do this code line a bit more efficiently . I’ll post again if I do. Deleting an item at specific position of index in an array sounds like something that could be often required.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete item at specific position of index in array

Post by HansV »

Beauty is in the eye of the beholder...
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Delete item at specific position of index in array

Post by Doc.AElstein »

.....Behold, I have seen the beauty, and it is in my coding....
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete item at specific position of index in array

Post by HansV »

:rofl:
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Delete item at specific position of index in array

Post by Doc.AElstein »

I think this next one is close to a direct translation of the full Evaluate one ( the one I could not do because the character count of the evaluate string was 352:
( =Mid(Left(",1,2,3,4,5", Find("|", Substitute(",1,2,3,4,5", ",", "|", 1))-1)&","&Right(",1,2,3,4,5,", Len("1,2,3,4,5") - (Find("|", Substitute(",1,2,3,4,5,", ",", "|", 2))-2)),2,Len(Left(",1,2,3,4,5", Find("|", Substitute(",1,2,3,4,5", ",", "|", 1))-1)&","&Right(",1,2,3,4,5,", Len("1,2,3,4,5") - (Find("|", Substitute(",1,2,3,4,5,", ",", "|", 2))-2)))) ) )

Code: Select all

 Sub DeleteItemByIndexIn1DArrayVBAAppSubSHG() '   https://eileenslounge.com/viewtopic.php?p=279881#p279881    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15218&viewfull=1#post15218
Dim Indx As Long '
 Let Indx = 1 ' 1 is for deleting the first element

Dim arr1D() As Variant
 Let arr1D() = Array(1, 2, 3, 4, 5)

Dim Ob As Object
 Set Ob = Application.WorksheetFunction
 Let arr1D() = Application.Index(Split(Mid(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Right$("," & Join(arr1D(), ",") & ",", Len(Join(arr1D(), ",")) - (InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") - 2)), 2, Len(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Right$("," & Join(arr1D(), ",") & ",", Len(Join(arr1D(), ",")) - (InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") - 2))) - 2), ","), 1, 0)
End Sub 
That seems much better. The Evaluate solution was clearly a bad idea. Using Substitute instead of Application.WorksheetFunction.Substitute was what attracted me to it, and I overlooked the significance of the 255 Evaluate string, which fortunately rosy reminded me of. Then luckily the Set Ob = Application.WorksheetFunction idea crossed my mind.
( I find that with this last one , when I use the horizontal scroll bar to view that long code line, it evokes pleasant thoughts in my mind of a lady skater meandering gracefully to and fro across a beautiful moonlit frozen lake. )
This next one below is almost the same as that last one above….. We have the advantage in VBA that the Mid function allows us to leave out the last argument , ( the one which is the length of the bit you want ). ( The spreadsheet MID function seems to want the last argument )
So, I am able then to use the MID function in the next macro in place of the Right function. Doing that in the long code line reduces a bit of the coding.

Code: Select all

Sub DeleteItemByIndexIn1DArrayVBAAppSubSHG2() '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35980&p=279809#p279809    https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15218&viewfull=1#post15218
Dim Indx As Long '
 Let Indx = 1 ' 1 is for deleting the first element

Dim arr1D() As Variant
 Let arr1D() = Array(1, 2, 3, 4, 5)

Dim Ob As Object
 Set Ob = Application.WorksheetFunction
 Let arr1D() = Application.Index(Split(Mid(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Mid$("," & Join(arr1D(), ",") & ",", InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") + 1), 2, Len(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Mid$("," & Join(arr1D(), ",") & ",", InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") + 1)) - 2), ","), 1, 0) '
End Sub
Last edited by Doc.AElstein on 20 Jan 2021, 07:05, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Delete item at specific position of index in array

Post by Doc.AElstein »

If we compare the 3 non Evaluate code lines, we can see that we have got the length down quite noticeably, so there is a chance it may work fairly well

Code: Select all

Application.Index(Split(Mid(Left$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1), 2, Len(Left$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "@") - 1) & "|" & Mid$(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), InStr(.Substitute(.Substitute("|" & Join(arr1D(), "|") & "|", "|", "#", Indx + 2), "|", "@", Indx + 1), "#") + 1)) - 2), "|"), 1, 0) '
Application.Index(Split(Mid(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Right$("," & Join(arr1D(), ",") & ",", Len(Join(arr1D(), ",")) - (InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") - 2)), 2, Len(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Right$("," & Join(arr1D(), ",") & ",", Len(Join(arr1D(), ",")) - (InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") - 2))) - 2), ","), 1, 0) 
Application.Index(Split(Mid(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Mid$("," & Join(arr1D(), ",") & ",", InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") + 1), 2, Len(Left$("," & Join(arr1D(), ","), InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx), "|") - 1) & "," & Mid$("," & Join(arr1D(), ",") & ",", InStr(Ob.Substitute("," & Join(arr1D(), ",") & ",", ",", "|", Indx + 1), "|") + 1)) - 2), ","), 1, 0)
Maybe some time later if I get a few more versions then I will do some speed tests and report back
If anyone else has any versions or practical data to test it on, then please post them and I will add them to my tests.

Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Delete item at specific position of index in array

Post by rory »

I'll never understand those who think that one line of code is inherently beautiful. All three of those are hideous to my eye... ;)
Regards,
Rory