Copying Filtered Items to an Array

[keepers123]

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]

Array = Range.SpecialCells Type:=xlCellTypeVisible

[snb]

@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
[vba]
Listbox1.list=sheets91).cells(1,100).currentregion.value
[/vba]

[Kenneth Hobs]

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.

[VBA]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
[/VBA]

[SamT]

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

[VBA]Set RngA = Range().SpecialCells Type:=xlCellTypeVisible
Array = RngA[/VBA]

But I didn't say that, did I?

[snb]

@Sam
Just try:

[vba]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[/vba]

[SamT]

Thanks.

That was unexpected.

And counter intuitive, at that.

[Aflatoon]

You can't return a contiguous range of discontiguous cells (ranges have to exist on a sheet somewhere)