How can I put values in one array into another without spaces?

[Brad24]

I have an array like:
sampleArr(1)=
sampleArr(2)= 123
sampleArr(3)=
sampleArr(4)=345

and because 1 and 3 doesn't have any values, I need to put it in another array, so it would look like

myArr(1)=123
myArr(2)=345

 

[Oaktree]

Use a counter and loop through your array.

Sub MakeNewArray()

Dim lngCounter As Long
Dim i As Long

lngCounter = 1

For i = LBound(sampleArr) To UBound(sampleArr)

    If Len(CStr(sampleArr(i))) > 0 Then

        myArr(lngCounter) = sampleArr(i)

        lngCounter = lngCounter + 1
    
    End If

Next i

End Sub

 

[dmt32]

hi,
untested but maybe something like this

Dim myArr() As Variant, Item As Variant
    Dim i As Integer
    For Each Item In samplearr
        If Len(Item) > 0 Then
        i = i + 1
        ReDim Preserve myArr(1 To i)
        myArr(i) = Item
        End If
    Next Item

I have assumed samplearr is a Variant array

Dave

 

[Brad24]

Hi, thans. How do I dimension the new array if I do not know how many indexes it needs in advance? It is telling me it isn't defined.

 

[Oaktree]

Hi, thans. How do I dimension the new array if I do not know how many indexes it needs in advance? It is telling me it isn't defined.

Since the new array will never be bigger than the previous array, you can set its ubound = to the ubound of the old array.

Sub MakeNewArray()

Dim lngCounter As Long
Dim i As Long
Dim myArr

Redim myArr(ubound(sampleArr)) 

lngCounter = 1

For i = LBound(sampleArr) To UBound(sampleArr)

    If Len(CStr(sampleArr(i))) > 0 Then

        myArr(lngCounter) = sampleArr(i)

        lngCounter = lngCounter + 1
    
    End If

Next i

then, Redim Preserve myArr(1 to lngCounter - 1)

Note that Dave's code would work too, but Redim Preserve within a loop can be VERY slow for a large array.

 

[Brad24]

When it hits the For, it gets values 1 to 14 but then when it goes to the If Len(CStr...)>0, it fails with a subscript out of range, for i=1 on the first pass

 

[Brad24]

I figured it out. My sampleArr() was 2D so I needed sampleArr(i,1)

Thanks for the code. Works perfect. @Dave, I didn't get a chance to try yours yet, but thanks for replying.

 

[dmt32]

I figured it out. My sampleArr() was 2D so I needed sampleArr(i,1)

Thanks for the code. Works perfect. @Dave, I didn't get a chance to try yours yet, but thanks for replying.

No worries - you found a solution that's the main thing.

Dave