edit dimension of array vba

Question

I have an array like this

dim arr(1 to 5) as string

arr(1)="a"
arr(3)="b"
arr(5) = "c"

(arr(2),arr(4) are empty).

How can I redim this arr(1to5) to exclude empty values and save also values "a","b","c" (I want the output like arr(1to3), arr(1)="a", arr(2)="b", arr(3)="c")? In general I do not know how many of them will be empty, so I need some general code for this (not for this specific example).

I was thinking about new temporary array to save all nonempty values and then redim arr(1to5).

Maybe it is a better (quick) way to do it?

I wrote sth similar:
Sub test()
Dim myArray() As String
Dim i As Long
Dim y As Long


ReDim Preserve myArray(3)
myArray(1) = "a"
myArray(3) = "c"

Dim myArray2() As String
y = 1
For i = LBound(myArray) To UBound(myArray)

If myArray(i) <> "" Then

ReDim Preserve myArray2(y)
myArray2(y) = myArray(i)
y = y + 1

End If
Next i

ReDim myArray(UBound(myArray2))
myArray = myArray2

End Sub

However I would like to avoid creating new array.

create a new array of the same size. Loop the first array and insert the values when not empty into the new array keeping track of the last spot with value in the new array, then redim preserve the new array to only the size that has values. — Scott Craner
– Scott Craner, Commented Mar 5, 2021 at 15:25
Also worth asking if maybe there is a design issue with the application. Can you let us know the bigger picture? — basodre
– basodre, Commented Mar 5, 2021 at 15:31
@EmiliaRajchel Posted a solution via negative filtering of the original array elements. — T.M.
– T.M., Commented Mar 7, 2021 at 13:23
@T.M. Sorry, I did not know that I have to mark this. Thanks for the advice. — Emilia Rajchel
– Emilia Rajchel, Commented Mar 25, 2021 at 9:47

Scott Craner · Accepted Answer · 2021-03-05 15:30:53Z

6

create a new array of the same size. Loop the first array and insert the values when not empty into the new array keeping track of the last spot with value in the new array, then redim preserve the new array to only the size that has values.

Sub kjlkj()
    Dim arr(1 To 5) As String
    
    arr(1) = "a"
    arr(3) = "b"
    arr(5) = "c"
    
    Dim newArr() As String
    ReDim newArr(1 To UBound(arr))
    
    Dim j As Long
    j = LBound(newArr)
    
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        If arr(i) <> "" Then
            newArr(j) = arr(i)
            j = j + 1
        End If
    Next i
    
    ReDim Preserve newArr(LBound(newArr) To j - 1)

    'do what you want with the new array.
    
    
End Sub

answered Mar 5, 2021 at 15:30

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FunThomas Over a year ago

Good solution, but probably I would prefer a Collection or Dictionary. BTW: Nice subroutine name - I use it also all the time ;-)

BigBen Over a year ago

I think I use asdfasdf or something similar haha.

T.M. Over a year ago

Helpful, especially as redimming after loop. - fyi posted a simple alternative via VBA's Filter() function.

T.M. · Accepted Answer · 2021-03-07 13:20:30Z

Alternative via Filter() function

"However I would like to avoid creating new array."

A negative filtering allows a basically simple alternative, however you have to

declare your array dynamically (i.e. without preset number of elements) to allow a rebuild overwriting the original array,
execute a double replacement over the joined array elements to allow insertion of a unique character that can be filtered out.

Sub testFilter()
    Dim arr() As String                                     
    ReDim arr(1 To 5)
    arr(1) = "a"
    arr(3) = "b"
    arr(5) = "c"
    'Debug.Print Join(arr, ",")                             ' ~~> a,,b,,c     

    'rearrange arr via RemoveEmpty()
    arr = RemoveEmpty(arr)                                  ' >>  function RemoveEmpty()
    Debug.Print Join(arr, ",")                              ' ~~> a,b,c

End Sub

Help function RemoveEmpty()

Adding an unused unique character, e.g. $, to the empty elements plus eventual negative filtering allows to remove these marked elements.

Note that the double replacement is necessary to allow to mark consecutive empty elements by the $ mark, as VBA would skip additional characters here.

Function RemoveEmpty(arr)
    Dim tmp
    tmp = Replace(Replace(Join(arr, "|"), "||", "|$|"), "||", "|$|")
    RemoveEmpty = Filter(Split(tmp, "|"), "$", False)
End Function

Collectives™ on Stack Overflow

edit dimension of array vba

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related