CODEWITHSUNDEEP
arraysvbaexcel
Fubudis
Fubudis

Reputation: 251

Remove element from array by position

I'm trying to figure out how to remove an element from an array if its in a certain position. We would also need to resize the array. To make the example easier, let's try remove whatever color is in position one in the array ("green", "blue", "red"). 

Sub Example()

Dim pos
colors = Array("Green", "Blue", "Red")

pos = 1

colors(pos).Delete 'obviously this doesn't work but what would the correct protocol be?

 End Sub

Upvotes: 3

Views: 14117

Answers (3)

brettdj
brettdj

Reputation: 55672

You could also use Filter (if the values were unique as in this example any string matching Green is removed).

Colors = Array("Green", "Blue", "Red", "Yellow", "Pink")
Colors = Filter(Colors, Colors(0), False)

Upvotes: 6

user4039065
user4039065

Reputation:

You can shift the values in the array starting at the pos then ReDim to its new dimensions.

Sub Example()

    Dim pos As Long, p As Long, vCOLOURs As Variant

    vCOLOURs = Array("Green", "Blue", "Red", "Purple", "Pink")

    For p = LBound(vCOLOURs) To UBound(vCOLOURs)
        Debug.Print p & ": " & vCOLOURs(p)
    Next p

    'vCOLOURs is an array dimmed 0 to 4
    pos = 3
    'vCOLOURs(pos) is 'Purple'

    For p = pos To UBound(vCOLOURs) - 1
        vCOLOURs(p) = vCOLOURs(p + 1)
    Next p
    ReDim Preserve vCOLOURs(0 To (UBound(vCOLOURs) - 1))

    For p = LBound(vCOLOURs) To UBound(vCOLOURs)
        Debug.Print p & ": " & vCOLOURs(p)
    Next p

End Sub

Results from the VBE's Immediate window (Ctrl+G)

example
0: Green
1: Blue
2: Red
3: Purple
4: Pink
0: Green
1: Blue
2: Red
3: Pink

Remember that the method you are using to put values into your variant array is creating a zero-based array. The first value is at vCOLOURs(0), not vCOLOURs(1).

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96753

You can set the specific element to a single blank and TRIM() it out of existence:

Sub DropFirstElement()
    Colors = Array("Green", "Blue", "Red")
    Colors(0) = " "
    Mystring = Application.WorksheetFunction.Trim(Join(Colors, " "))
    Colors = Split(Mystring, " ")
End Sub

Note:

We use the worksheet's Trim() function rather than VBA's Trim() function to remove multiple sequential spaces.

Upvotes: 5

Related Questions