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

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

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