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

 

[Blade Hunter]

This should help you with the sorting.

http://www.cpearson.com/excel/SortingArrays.aspx

Cheers

Dan

 

[maniknath]

yeah true, but it never came to my mind

 

[lord shumac]

Hi xld,

thanks for your help. its working now. thank you

Manik

How did you get it working? I am having the same issue. Subscript out of range...

 

[Blade Hunter]

Check no references are "missing" in tools / references. If there are turn them off.

Cheers

Dan

 

[lord shumac]

Check no references are "missing" in tools / references. If there are turn them off.

Cheers

Dan

Thanks, got it working.