Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter - which rows are currently visible?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter - which rows are currently visible?
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 FormatConditional 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. Bjørnar Hartviksen wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter - which rows are currently visible?
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. -- Regards, Tom Ogilvy Bjørnar Hartviksen wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel copy only visible rows from autofilter | Excel Discussion (Misc queries) | |||
how can i choose only visible cells from a autofilter? | Excel Worksheet Functions | |||
Copy to visible cell after Autofilter | Excel Discussion (Misc queries) | |||
autofilter sort not visible | Excel Discussion (Misc queries) | |||
countif for only visible rows when combined with autofilter - possible? | Excel Discussion (Misc queries) |