Efficient Arrays?

[Mark]

Efficient Arrays: Setting size equal to count?
If I have an array that, based on the data loaded to the program, could need
to be rather small(10) or very large (10000), if there a way to selectively
set the size.

If I made the array dynamic, can I redim = count where count is a variable
based on the number of rows in a column?

Or is there no harm in dim an array as very large an only using a small part
of it? Does the unused portion pose a large drain on resources?

 

[Bob Phillips]

Always best to set as required, it is a once-off operation.

As you guessed, you can ReDim it

Dim ary()
Dim rng As Range

Set rng = Range("A1:A100")

ReDim ary(1 To rng.Rows.Count)


or more dynamically

Dim ary()
Dim iLAstRow As Long

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

ReDim ary(1 To iLastRow)


You can even change it later if need be, and preserve the contents

ReDim Preserve ary(1 To 100)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim Thomlinson]

redim is probably the best option here. If you know the size of the array at
runtime then use the redim statement to set the size. (redim preserve if you
want to change the size of an existing populated array) Don't make an array
the is huge to store a small amount of data. If you do you will not
effectively be able to traverse the array looking for an item in the array.
Also it is a huge waste of memory.