Arrays [Why does this not work?]

[AshofMind]

Dim count ' counter
Dim people() As String 'array

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.count - 1 'Subtract header

Max = row_count 'array is this big
ReDim people(1 To Max) 'redim array

For count = 1 To Max
While Not IsEmpty(ActiveCell)
count = count + 1
ActiveCell.Offset(1, 0).Select
people(i) = ActiveCell.Value
Wend
Next count

 

[Gary Keramidas]

don't know what else you want to accomplish, but this may work for you:

Sub test()
Dim arr As Variant
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
arr = ws.Range("A2:A" & lastrow)

MsgBox UBound(arr)
End Sub

 

[AshofMind]

Thank you for the help. Thats running correctly now

I was attempting to create an array filled with a list of names( list
can get bigger or smaller)
Then I am going to be passing the names through an autofilter to parse
the list

I got the parsing section working but have had no luck on getting data
into an array

 

[Dave Peterson]

You're looping within your "for/next" loop.

I wouldn't use a variable named count, either. It looks too much like the
..count property. It may not confuse excel, but it would confuse me:

Option Explicit
Sub testme()


Dim myCount ' counter
Dim people() As String 'array
Dim Row_Count As Long
Dim Max As Long
Dim i As Long

'find how many people listed in sheet 2 column A
Sheets("Sheet2").Select
Range("a1").Select
Selection.CurrentRegion.Select
Row_Count = Selection.Rows.count - 1 'Subtract header

Max = Row_Count 'array is this big
ReDim people(1 To Max) 'redim array
i = 0
For myCount = 1 To Max
If IsEmpty(ActiveCell.Value) Then
'skip it
Else
i = i + 1
people(i) = ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Next myCount

If i = 0 Then
MsgBox "no cells added"
Else
ReDim Preserve people(1 To i)
End If

End Sub

 

[Dave Peterson]

If you want a list of unique names, you could use data|filter|advanced (on
another sheet???).

Or John Walkenbach shows how to loop through a range to get the unique values
here:
http://j-walk.com/ss/excel/tips/tip47.htm

 

[Gary Keramidas]

wouldn't this variation of my original post give a list of unique names?

Sub test()
Dim unique_names As New Collection
Dim lastrow As Long
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next

For i = 2 To lastrow
unique_names.Add ws.Range("A" & i), CStr(ws.Range("A" & i))
Next

On Error GoTo 0
MsgBox unique_names.Count
End Sub

 

[AshofMind]

Thank you so much thats exactly what I was attempting to do

 

[Mike Fogleman]

This should do the trick:

Sub test()
Dim people() As String 'array
Dim Max As Long
Dim i As Integer

'find how many people listed in sheet 2 column A
'Sheets("Sheet1").Select
'Range("a1").Select
'Selection.CurrentRegion.Select
Max = Sheet1.Cells(Rows.count, 1).End(xlUp).Row - 1 'Subtract header
'array is this big
ReDim people(1 To Max) 'redim array
For i = 1 To Max
people(i) = Cells(i + 1, 1)
Next i

'this will put the array back in col C
For i = 1 To Max
Range("C" & i + 1).Value = people(i)
Next
End Sub

Mike F

 

[Dave Peterson]

Probably (I didn't test, but it looks very much like John Walkenbach's example
to me).

But you may not want to do this:
Dim unique_names As New Collection

Chip Pearson explains why:
http://www.cpearson.com/excel/variables.htm
Look for:
Don't Use The New Keyword In A Dim Statement

 

[Gary Keramidas]

so, is he saying to use something like this?

Dim unique_names As Collection
Set unique_names = New Collection

 

[Dave Peterson]

Yep.

so, is he saying to use something like this?

Dim unique_names As Collection
Set unique_names = New Collection

 

[Gary Keramidas]

ok, thanks dave.

--


Gary

Yep.