Build Array

[don]

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

 

[Mike]

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

 

[Rick Rothstein]

First off, concatenation is the process of taking two or more pieces of text
and joining them together into a single piece of text, so that is not what
you would want to do in order to make an array. Secondly, you appear to be
trying to create an array of row numbers. I'm not sure that makes any sense
to do as you can easily find a first and last row number and know all the
row numbers between them... there is no need to make an array for that
purpose. Perhaps if you tell us what you ultimate goal is, maybe someone
here will be able to offer you an different, more efficient, method than you
seem to now have in mind.

 

[don]

Thanks Mike.
that fit the bill.
did what I needed.

 

[Chip Pearson]

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)

 

[Mike]

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

 

[Chip Pearson]

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)

 

[Mike]

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