Do until? For next? Help

[Jeff Smith]

Hi

I need to use to autofilter using criteria contained in a list.

The variable data (Cell A1, Cell A2 etc.) is presently contained in the
range A1 to A20. Sometimes there may be only (say) 7 items but other times
the list may be 19 items. It will never equal or exceed 20.

e.g.

Selection.AutoFilter Field:=3,Criteria1:="Cell A1" (Apples)
Selection.AutoFilter Field:=3,Criteria1:="Cell A2" (Pears)
Selection.AutoFilter Field:=3,Criteria1:="Cell A3" (Oranges)

ending when

Selection.AutoFilter Field:=3,Criteria1:="Empty Cell in list"

After each filter operation, I copy the resultant related information to
another location (in the same spreadsheet using myCopy macro) then proceed
to filter the next criteria.

I have tried to record the macro but it literally records the value of the
contents. The contents vary so I need a macro that starts at Cell A1 to
determine the first filter criteria and stops when the cell in the list is
blank.

I have posted earlier and have resolved other aspects of my problem
(thanks). This query is the last hurdle to overcome and it is beyond my VBA
knowledge.

TIA

Jeff Smith

 

[Myrna Larson]

Since you need to handle the situation where there are only 0 or 1 entries in
the list, I think Do While/Loop is the cleanest.

Dim R As Long
R = 1
Do While Not (IsEmpty(Cells(R, 1).Value))
Selection.AutoFilter Field:=3, Criteria1:=Cells(R, 1).Value
'code to do the copy
R = R + 1
Loop