Remove Empty array items in Excel VBA

[maniknath]

Hi All,

I'm stuck with an array problem in excel. I have an array MyArr() in excel.

Its length is from 1 to i where i takes dynamic value from varibable.

Now the problem is MyArr(1 to i) has some empty values.

Like say: if i = 5 then
MyArr(1) = "a"
MyArr(2) = ""
MyArr(3) = "b"
MyArr(4) = ""
MyArr(5) = "c"

How can I get rid of those empty elements so that MyArr() becomes only three elements long and then display them in a range of three cells.

Thanks in advance for any help.

Manik

 

[maniknath]

hi,

sorry forgot to mention i'm doing this in vba

Thanks again

 

[Peter_SSs]

How did MyArr get populated in the first place?

 

[xld]

MyArr(1) = "a"
MyArr(2) = ""
MyArr(3) = "b"
MyArr(4) = ""
MyArr(5) = "c"

ReDim NewArr(LBound(MyArr) To UBound(MyArr))
For i = LBound(MyArr) To UBound(MyArr)
    If MyArr(i) <> "" Then
        j = j + 1
        NewArr(j) = MyArr(i)
    End If
Next i
ReDim Preserve NewArr(LBound(MyArr) To j)
Range("A1").Resize(j - LBound(NewArr) + 1) = Application.Transpose(NewArr)

 

[maniknath]

Hi xld,

thanks for the reply. but what is the value of 'j'? coz' when i run this code it return an error of "subscript out of range".

Hi Peter,

MyArr() got populated from a string and then I performed an operation which compared this array with another array and delete out the common characters. So I was left with this array which has some elements populated and some elements empty. So need to get rid of the empty elements and have an array that has only the filled elements.

Thanks for your help.

Manik

 

[xld]

Hi xld,

thanks for the reply. but what is the value of 'j'? coz' when i run this code it return an error of "subscript out of range".

j is a counter for the newly created array, telling it the index of the next item.

 

[maniknath]

Hi xld,

thanks for your help. its working now. thank you

Manik

 

[Blade Hunter]

Does your array need to be in a particular order? Sorting it would bubble all the blanks up to the top where you could bulk remove them.

 

[maniknath]

Hi Blade,

No not really. i just want the non-empty elements of the array. that's it.

Thanks,

Manik

 

[xld]

Actually, if the order is irrelevant, why not just drop the whole array on a range as is, and then sort it. The blanks then effectively disappear.