Arrays problem in new filtering code

[Steve]

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).Address
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.

 

[Rick Rothstein]

Why are you using such an awkward loop structure. You know the limits you
want to iterate between, so why not just use a For..Next loop instead to
loop between them? Starting with your i=1 statement, here is some revised
code for you to consider...

i = 1
Set MyRange = Worksheets("Sheet1")
EndPoint = MyRange.Cells(MyRange.Rows.Count, "E").End(xlUp).Row
For Count = 6 To EndPoint
If Sheets("Sheet1").Rows(Count).Hidden = False Then
CCIndex(i) = Left(MyRange.Range("E" & Count).Value, 11)
i = i + 1
End If
Next

Note that I made a couple of changes (personal preference)... I changed your
EndPoint variable to hold the Row number rather than the address. I then use
that as the upper limit in the For..Next loop that follows (the lower limit
was set at 6 which is what your code showed your starting your count at). I
also changed your Range("E65536") reference to a Cells call and let VB
calculate the 65536 number via Rows.Count (again, personal preference... I
never can remember that number on my own<g>). Also, since you set the
worksheet in the MyRange variable (why that name... it is not a range, it is
a worksheet), I reversed the Set statement and EndPoint assignment statement
in order to take advantage of the Set variable (doing this allows me to use
MyRange instead of having Worksheets("Sheet1") repeated twice).

Anyway, the main point of the code it that I think the For..Next loop is
cleaner and easier to construct/maintain than the Do..Loop you originally
posted. Note that you might be able to eliminate the declaration for
MyAddress if you don't make use of it anywhere else in your code.

 

[Steve]

Thanks Rick. This is my first attempt at using an array. I have only been
programming spreadsheets for a while. I tried a few other ways around it but
things ran slow or did not work. I knew more or less what an array was, but I
wasn't sure the best way t use them. I know that Do...Loop looked funny, it
as mostly because I was more concerned with making my array work right. If
you noticed the GetLimit() function in there, that is another loop I used
that looks just as bad as the one you saw. All it does is allow me to ReDim
my array, I'm use you gathered that though. At one point both loops used For
loops, but then all sense left me as I tried to get that array to fill up the
way I wanted.

 

[Rick Rothstein]

You can nest For..Next loops if needed. For example, consider this example
macro which fills in cells on the active sheet showing which loop variable
is active at which time through the loops...

Sub test()
Dim X As Long
Dim Y As Long
For X = 1 To 5
For Y = 100 To 110
Range("A1").Offset(X - 1, Y - 100).Value = "X = " & X & ", Y = " & Y
Next
Next
End Sub