I use Excel '03. There are two sheets with different projects we are working
on. Column E on both sheets have identical information; an alpha-numeric code
formated xxxxx-xxxxx. What I am trying to make happen is when I click on
Sheet 2, it filters column E to those codes that are currently being
displayed on Sheet 1 through the activate event. Sheet 1 has a seperate
column with project owners names on it that I use to filter the sheet by one
name at a time and look at the information for all of the owners projects.
One code per project.

I am trying to get all the codes from Sheet 1 to store into an array but my
loop exits when I get to the last cell in the range. I am trying to use a
dynamic array that uses range(e65536).end(xlup).address as the upper limit of
the array. Here is the code. Thanks for any help.

Dim CCLimit As Integer
Dim CCIndex() As String
Dim MyRange As Object
Dim EndPoint As Variant
Dim Count As Long, i As Long
Dim R As Object

CCLimit = GetLimit()
ReDim CCIndex(1 To CCLimit)

i = 1
Count = 6
EndPoint = Worksheets("Sheet1").Range("E65536").End(xlUp).Add ress
Set MyRange = Worksheets("Sheet1")
MyAddress = MyRange.Range("E" & Count).Address
Do
If Sheets("Sheet1").Rows(Count).Hidden = False Then
CCIndex(i) = Left(MyRange.Range("E" & Count).Value, 11)
MsgBox CCIndex(i)
i = i + 1
End If
Count = Count + 1
MyAddress = MyRange.Range("E" & Count).Address
Loop Until MyAddress = EndPoint

All I need to know at this point is how to get the array to work. I tried to
change Loop Until MyAddress = EndPoint
to
Loop Until MyAddress EndPoint
but that did not work.