Variable Upper index of an Array

[abi_learn_vba]

Hi,

I have the below code where the Upper index of an array is not constant. The upper index of an array is decided based the value incremented for the variable "ar". So i have to use Redim every time to define the size(Upper-Index) of the array. The Values in Column "D" are e-mail ids which is stored in the array.

My problem is, when "ar = 1" the first e-mail id is stored in myarray(1), later when "ar" is increment by "1" and is equal to "2" then the second e-mail id is stored in myarray(2) but since i have redim the array with new upper index the value stored in myarray(1) is cleared and is empty. Am not sure how to go about with this scenario.

For i to Range("A" & Rows.count).End(xlup)
m = Range("A1").Value
n = Range("B1").Value
If m <> n Then
ar = ar + 1
ReDim myarray(1 To ar) As Variant
myarray(ar) = Range("D" & i).Value
End If

For j = LBound(myarray) To UBound(myarray)
mailinglist = myarray(j) & ";" & mailinglist 
Next

Any help would be appreciated. let me know if you are not clear with my question.

Thanks
-Abi

 

[Gary McMaster]

You must use "Redim Preserve" to prevent the existing elements of the array from being cleared.

Please see VBA help for the "preserve" keyword.

Gary

 

[abi_learn_vba]

Thanks a Lot Gary, that helped me. I did not knew this before.

Cheers..
-Abi