Expert: Tom Ogilvy - 3/10/2008

Question
I have a worksheet "Database" that contains a master project list with each project listed on a separate row and columns for Project Completed, Unit, Site, Category, etc.  I can use VBA to filter the list for all Completed projects at Site aaa, in Unit xxx, in Category zzz.  Once the list is filtered I would like to select the first filtered row and run code then move to the next filtered row and run code, etc. until I get to the end of the filtered list.  How to I navigate through the filtered list (some rows will be adjacent but others will not)?

Answer
Dim rng as Range, cell as Range
' get the range of data that is filtered, then
' reference only the first column of the range
set rng = Activesheet.Autofilter.Range.Columns(1).cells
' adjust the range so only the data is included, not
' the header row
set rng = rng.offset(1,0).Resize(rng.rows.count-1,1)
' now loop through the cells
for each cell in rng
 ' check if the row is visible - if so, process it
 if cell.EntireRow.Hidden = False then
  ' process this row
 end if
Next

You could also use special cells such as

set rng = rng.offset(1,0).Resize(rng.rows.count-1,1)
set rng1 = rng.SpecialCells(xlVisible)
for each cell in rng1
 ' process the row for each cell
Next

but you would have to also check that there are visible rows or you would get an error.


--
Regards,
Tom Ogilvy