Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: determine if value is invisible in autofilter
Hi,
I am looking for a vba solution to put de values of a selected range on a worksheet into an array, listbox or combobox. The issue is as follows: When a user uses the autofilter to produce a filtered list and subsequently selects and copys a range then only the values of the cells that were visible after the autofilter was set will be copied. However when i select a filtered range and then execute a procedure that puts the value of each of de cells in the selected range into a listbox, then also values that where not visible when the autofilter was set will be put into the listbox (or array or combobox). so to better formulate my question: When i am using a for each loop from say row 1 to row 20 to put each value in column A into a listbox, how can i exclude those values that are not visible when autofilter is set? Thanx a lot! Tamar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: determine if value is invisible in autofilter
Let's say your range is in myRange.
for each r in myRange if not r.entirerow.hidden then 'Put what you'd do if the row wasn't hidden end if next r "Tamar" wrote: Hi, I am looking for a vba solution to put de values of a selected range on a worksheet into an array, listbox or combobox. The issue is as follows: When a user uses the autofilter to produce a filtered list and subsequently selects and copys a range then only the values of the cells that were visible after the autofilter was set will be copied. However when i select a filtered range and then execute a procedure that puts the value of each of de cells in the selected range into a listbox, then also values that where not visible when the autofilter was set will be put into the listbox (or array or combobox). so to better formulate my question: When i am using a for each loop from say row 1 to row 20 to put each value in column A into a listbox, how can i exclude those values that are not visible when autofilter is set? Thanx a lot! Tamar |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: determine if value is invisible in autofilter
Dear Barb Reinhardt,
thanx for your response, you helped me a lot! i made the unfortunate assumtion that the hidden property of a row that is hidden is different from a property that makes a filtered row hidden. greetz, Tamar "Barb Reinhardt" wrote: Let's say your range is in myRange. for each r in myRange if not r.entirerow.hidden then 'Put what you'd do if the row wasn't hidden end if next r "Tamar" wrote: Hi, I am looking for a vba solution to put de values of a selected range on a worksheet into an array, listbox or combobox. The issue is as follows: When a user uses the autofilter to produce a filtered list and subsequently selects and copys a range then only the values of the cells that were visible after the autofilter was set will be copied. However when i select a filtered range and then execute a procedure that puts the value of each of de cells in the selected range into a listbox, then also values that where not visible when the autofilter was set will be put into the listbox (or array or combobox). so to better formulate my question: When i am using a for each loop from say row 1 to row 20 to put each value in column A into a listbox, how can i exclude those values that are not visible when autofilter is set? Thanx a lot! Tamar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
AutoFilter - How to determine number of records found | Excel Programming | |||
determine if autofilter is on | Excel Programming | |||
Determine Value of Autofilter | Excel Discussion (Misc queries) | |||
Invisible AutoFilter Drop-Down Arrows | Excel Programming |