What is a good way to create a variably sized group to be looped over in Excel 2003?

Question

I have a procedure that is run for a lot of items, skipping over certain items who don't meet a criterion. However, I then go back and run it for some of the individuals who were missed in the first pass. I currently do this by manually re-running the procedure for each individual person, but would ideally like a solution a little more hands off.

Something my boss suggested might be effective would be creating a List (as in Data -> Lists) that contains the names of the items in question, and then iterating over the list. Sadly, my help-file fu seems to be failing me - I don't know whether I just don't know what to look for, or what.

Running the "Generate Macro" command shows that the VBA to create a list in the first place is along the lines of ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1"), , xlYes).Name = "List1"

Unfortunately, I can't seem to figure out how to then do stuff with the resulting list. I'm looking to making a loop along the lines of

For Each ListItem in List 
    Run the procedure on the text in ListItem.Value
Next ListItem

Any suggestions?

Answer 1

You can iterate on it in this way:

set rgList = Range("name_of_range")    
For i = 1 To rgList.Rows.Count
  ' Do something using rgList.Cells(i, 1)
  RunProcedure(rgList.Cells(i, 1))
Next i

I assumed here that the range is on a column; was it on a row, you should have done the iteration on the second index.
Of course, there may be better ways to iterate on a range; I am using this one on a small script, and is working quite fine.

Answer 2

Perhaps something on these lines:

Dim Counter 'module level '

Sub RunSomeProc()
    Counter = 0
    '1st test '
    SomeProc

    '2nd Test skipped items'
    For Each c In Range("c1:c" & Counter)
        SomeProc
    Next

End Sub

Sub SomeProc()
For Each c In Range("NamedRange1")
    If SomeTest=SomeVal Then
        'Write to 2nd test range '
        Range("C1").Offset(Counter, 0) = c 'Value of cell'
        Counter = Counter + 1
    End If
Next
End Sub

Answer 3

My eventual solution was to import the list as an external data query that I then named nicely and referenced as a range. So:

For each item in Sheets("Sheet1").Range("Range1")
    Do stuff
Next item