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

Brad24

Board Regular
Joined
May 4, 2015
Messages
81
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Use a counter and loop through your array.

Code:
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
 
Upvote 0
hi,
untested but maybe something like this

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.

Code:
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.
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,373
Messages
6,171,693
Members
452,418
Latest member
kennettz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top