PDA

View Full Version : Copying Filtered Items to an Array



keepers123
05-29-2013, 08:14 AM
Hi,

I am having a problem with adding the items i have filtered to an array.

Problem: I have applied multiple filters to a spreadsheet to meet my requirements. I have a certain drop down list that i want it to be copied into an array after all the filtering is done.



I want all the items of this filtered list into an array.

Hope its not confusing. Thanks for your help.

SamT
05-30-2013, 04:43 AM
Array = Range.SpecialCells Type:=xlCellTypeVisible

snb
05-30-2013, 04:57 AM
@SamT

That won't do the whole trick: only the first area will be written into the array.
You can copy the filtered range to a certain cell. Now it's a range withou empty rows/columns. You can put that range directly into a listbox or combobox using

Listbox1.list=sheets91).cells(1,100).currentregion.value

Kenneth Hobs
05-30-2013, 06:17 AM
Since C1 is the Filter column heading, I start at C2. Filter only works down to the first empty row which I guess is what you want.

Sub ken()
Dim r As Range
Set r = Range("C2", Range("C2").End(xlDown)).SpecialCells(xlCellTypeVisible)
MsgBox r.Address

Dim a() As Variant
a() = r
MsgBox Join(WorksheetFunction.Transpose(a), vbLf)
End Sub

SamT
05-30-2013, 07:00 AM
only the first area will be written into the array

Thanks, didn't realize that Range.SpecialCells Type:=xlCellTypeVisible would would not return a contiguous range of only the visible cells.

Or is there something else going on?

Actually I was thinking of

Set RngA = Range().SpecialCells Type:=xlCellTypeVisible
Array = RngA

But I didn't say that, did I?

snb
05-30-2013, 07:24 AM
@Sam
Just try:

Sub M_snb()
with sheets(1).cells(1).currentregion
.autofilter 1, "sam"
sn=.offset(1).specialcells(12).value
combobox1.list=.offset(1).specialcells(12).value
.offset(1).copy sheets(1).cells(1,40)
.autofilter
end with
Combobox2.list=sheets(1).cells(1,40).currentregion.value

msgbox combobox1.listcount & vblf & combobox2.listcount & vblf & ubound(sn)
End Sub

SamT
05-30-2013, 01:08 PM
Thanks.

That was unexpected.

And counter intuitive, at that.

Aflatoon
06-03-2013, 06:58 AM
You can't return a contiguous range of discontiguous cells ;) (ranges have to exist on a sheet somewhere)