Do until? For next? Help

  • Thread starter Thread starter Jeff Smith
  • Start date Start date
J

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
 
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
 
Back
Top