Way to compact an array?

[sjh]

Hi,
Is there an easy way to compact an array? For example, if aConn(2) contains nothing, shuffle the next values in array and redim the whole array.

Thank you,
Susie

 

[PHV]

If you want dynamic arrays, why not consider the Collection object ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Susie,

So you don't care where the value is stored in the array?
[tt]
Dim aConn()
ReDim aConn(8)
r = 1
For i = LBound(aConn, 1) To UBound(aConn, 1)
aConn(i) = Cells(r, 1).Value
r = r + 1
Next
iCnt = UBound(aConn, 1)
For i = LBound(aConn, 1) To UBound(aConn, 1)
If aConn(i) = "" Then
For j = UBound(aConn, 1) To LBound(aConn, 1) Step -1
If aConn(j) <> &quot;&quot; Then
aConn(i) = aConn(j)
iCnt = iCnt - 1
Exit For
End If
Next
End If
Next
ReDim Preserve aConn(iCnt)
For i = LBound(aConn, 1) To UBound(aConn, 1)
ActiveCell = aConn(i)
ActiveCell.Offset(1, 0).Select
Next
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884