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

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

--


Gary


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> 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
>
> --
>
> Gary
>
> <AshofM...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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

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

(E-Mail Removed) wrote:
>
> 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

--

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



"(E-Mail Removed)" wrote:
>
> 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
>
> On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> > 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
> >
> > --
> >
> > Gary
> >
> > <AshofM...@gmail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> > > 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

--

Dave Peterson

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

--


Gary


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> "(E-Mail Removed)" wrote:
>>
>> 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
>>
>> On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
>> > 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
>> >
>> > --
>> >
>> > Gary
>> >
>> > <AshofM...@gmail.com> wrote in message
>> >
>> > news:(E-Mail Removed)...
>> >
>> > > 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
>
> --
>
> Dave Peterson

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

On Sep 12, 2:59 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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
>
>
>
> AshofM...@gmail.com wrote:
>
> > 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
>
> --
>
> Dave Peterson

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
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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 wrote:
>
> 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
>
> --
>
> Gary
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >
> >
> > "(E-Mail Removed)" wrote:
> >>
> >> 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
> >>
> >> On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> >> > 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
> >> >
> >> > --
> >> >
> >> > Gary
> >> >
> >> > <AshofM...@gmail.com> wrote in message
> >> >
> >> > news:(E-Mail Removed)...
> >> >
> >> > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

so, is he saying to use something like this?

Dim unique_names As Collection
Set unique_names = New Collection


--


Gary


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
>>
>> 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
>>
>> --
>>
>> Gary
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> >
>> >
>> > "(E-Mail Removed)" wrote:
>> >>
>> >> 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
>> >>
>> >> On Sep 12, 2:43 pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
>> >> > 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
>> >> >
>> >> > --
>> >> >
>> >> > Gary
>> >> >
>> >> > <AshofM...@gmail.com> wrote in message
>> >> >
>> >> > news:(E-Mail Removed)...
>> >> >
>> >> > > 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
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson