Expert: Tom Ogilvy - 3/12/2008

Question
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



---------- FOLLOW-UP ----------

QUESTION: Tom - I used the first option you suggested and it worked great. I am trying to
adapt it to delete rows in the filtered list.  When I added a
cells(cell.row,1).entirerow.delete statement in the process row area of the For
Each cell in rng loop I get crossed up because once the first row is deleted
the rows renumber.  Is there a way to reverse step through the loop so
deleting rows won't change the row number for rows not yet acted on?

Answer
I would use something like this (this particular code is untested and may contain typos but should show the direction):

Dim StrtRow as Long, LstRow as Long
Dim i as Long
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
' find first and last row
StrtRow = rng(1).row
LstRow = rng(rng.count).row
' loop backwards through range
for each i = LstRow to StrtRow Step -1
' check if the row is visible - if so, process it
if rows(i).EntireRow.Hidden = False then
 ' process this row
   rows(i).Delete
end if
Next

As before, You could also use special cells but in this case, you can do the deletion without looping

Dim rng as Range, rng1 as Range
set rng = Activesheet.Autofilter.Range.Columns(1).cells
set rng = rng.offset(1,0).Resize(rng.rows.count-1,1)
On Error Resume next
set rng1 = rng.SpecialCells(xlVisible)
On Error goto 0
if not rng1 is nothing then
rng1.EntireRow.Delete
else
 msgbox "No visible rows in filter"
End if

--
Regards,
Tom Ogilvy