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.
Array = Range.SpecialCells Type:=xlCellTypeVisible
@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
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?
@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
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)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.