LOL... I saw that it was misspelled and changed it but it still didnt work. I
reopened the workbook and now it is working.

"Chip Pearson" wrote:

I get a script out of range error with your suggestion.

Shame on me for not testing the code, and shame on you for not using
Option Explicit.

ReDim arrexcevalues(1 To rng.Cells.Count)
should be
ReDim arrexcelvalues(1 To rng.Cells.Count)

The name of the array is misspelled.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 17 Nov 2008 05:26:01 -0800, Mike
wrote:

Chip
I get a script out of range error with your suggestion.

"Chip Pearson" wrote:


For Each c In rng
ReDim Preserve arrExcelValues(x)

ReDim Preserve is an expensive operation and should be avoided if at
all possible. Since you already know how many elements there will be
(rng.Cells.Count), you can use a single ReDim to size the array
appropriately. For example,


Sub Array2()
Dim arrExcelValues() As Variant
Dim rng As Range
x = 0
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
ReDim arrexcevalues(1 To rng.Cells.Count)
For Each c In rng
x = x + 1
arrExcelValues(x) = c.Value
Next c

End Sub


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 16 Nov 2008 18:23:05 -0800, Mike
wrote:

This looks at Column A
Sub loopArray()
Dim arrExcelValues()
Dim rng As Range
x = 0

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each c In rng
ReDim Preserve arrExcelValues(x)
arrExcelValues(x) = c.Value
x = x + 1
Next
For Each strItem In arrExcelValues
MsgBox strItem
Next

End Sub

"don" wrote:

I would like to build an array by looping thru a set of criteria.
When finished use the array to do some calculations based on the
length of the array.

However when I try to build an array by concatentating the rows found,
ie rownum= rownum & "," & d.row
the result is "11 , 12"
which the array command views as 1 item in the array when I want it to
be 2 items, namely 11 and 12.
the result of msgbox ubound(array(rownum)) is 0
How do I concatenate rows found into an array.

Thanks