AboutTom Ogilvy Expertise Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
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
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
