vba clearing out values stored in array

[chick-racer]

I have an array in a loop.
The first pass thru the loop it collects values and stores them in the
array, if i have conditions set for the second pass thru the loop and
the array doesnt reset(so to speak) and no values are to be stored, i
find that the array still carries all the old values from the first
pass.

My question is, how can i make sure my array is empty before it goes
thru the loop again??
here is a bit of what i have for code: the array varies in size each
time.

Set rng = Range("D" & J + K, "N" & J + K)
count = 1

For Each c In rng


If (Not IsEmpty(c) And IsNumeric(c)) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
ReDim Preserve meanArray(1 To count)

meanArray(count) = c.Value
count = count + 1
End Select

thanks alot for everyones help.

 

[Tom Ogilvy]

Set rng = Range("D" & J + K, "N" & J + K)
count = 1

' since you know the max number of elements, do a redim with
' no preserve
ReDim meanArray(1 to rng.Count)
For Each c In rng
If (Not IsEmpty(c) And IsNumeric(c)) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
meanArray(count) = c.Value
count = count + 1
End Select
end if
Next

Redim Preserve meanArray(1 to count-1)



--
Regards,
Tom Ogilvy

I have an array in a loop.
The first pass thru the loop it collects values and stores them in the
array, if i have conditions set for the second pass thru the loop and
the array doesnt reset(so to speak) and no values are to be stored, i
find that the array still carries all the old values from the first
pass.

My question is, how can i make sure my array is empty before it goes
thru the loop again??
here is a bit of what i have for code: the array varies in size each
time.

Set rng = Range("D" & J + K, "N" & J + K)
count = 1

For Each c In rng


If (Not IsEmpty(c) And IsNumeric(c)) Then
Select Case c.Font.ColorIndex
Case xlAutomatic, 1
ReDim Preserve meanArray(1 To count)

meanArray(count) = c.Value
count = count + 1
End Select

thanks alot for everyones help.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[Tom Ogilvy]

Also look in Excel VBA help at ERASE.