AutoFilter - which rows are currently visible?

[Bjørnar Hartviksen]

Problem with Visual Basic for Excel 2000:
I have an Excel model containing a list/table area. On this list I use the
AutoFilter feature, which allows me to show only the entries currently of
interest. What I need is a method (using VBA) to determine whether or not
any given row in the table is currently visible (or filtered out).
Alternatively, I need another method to solve this problem: Since there are
quite a few columns in my table, I want to use a different background color
on every other row to give the user a better overview. This is not
difficult, but when the user changes the filters, the whole layout is
destroyed. How can I keep the pattern?

All suggestions appreciated
hartvix

 

[Debra Dalgleish]

You can do this if you add a helper column to the table:

1. Insert a blank column (column A in this example)
2. Add a heading, e.g. Count
3. In row 2, enter the following formula, where column B
contains no blank cells within the table range:
=SUBTOTAL(3,$B$2:$B2)
4. Copy the formula down to all rows of data
5. Select the worksheet
6. Choose Format>Conditional Formatting
7. Choose Formula Is
8. In the formula box, type:
=AND($A1<>"",MOD($A1,2)=0)
9. Click the Format button, and select a colour for the shaded rows
10. Click OK, click OK

Filter the table, the count will change, and alternate rows will be shaded.

 

[Tom Ogilvy]

testrow = Rows(21)
set rng = Activesheet.Autofilter.range.Columns(1)
if not Intersect(rng.specialCells(xlVisible),Testrow) is nothing then
' the specified row is visible
End if

Depends on how you colored your rows. If you looped through them and
colored them, then you would have to do that each time the filter criteria
is changed, checking which rows are visible and keeping track of whether to
color the row or not.