Populate ListBox from filtered list

  • Thread starter Thread starter Loomah
  • Start date Start date
L

Loomah

Hi All
I have the following code to attempt to populate a listbox from a filtered
list (Auto Filter, 2 columns) but it fails if there is a filter in place and
only uses the first Area in populating the ListBox

Private Sub UserForm_Initialize()
'(failed) attempts to take data from a filtered list
'and use it to populate a 2 column listbox
Dim myArray() As Variant

'attempt to write values into array
'fails as only first area is written into array
myArray = Worksheets(1).Range("A2:b" & _
Cells(65536, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Value

With Me.ListBox1
'make 2 columns in listbox
.ColumnCount = 2
'populate
.List() = myArray
End With
End Sub

any help, pointers, suggestions greatly appreciated
TIA
 
Note that Range("A2:b" is changed to Range("A2:A"

Private Sub UserForm_Initialize()
Dim cell as Range, rng as Range

'attempt to write values into array
'fails as only first area is written into array
set rng = Worksheets(1).Range("A2:A" & _
Cells(65536, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Value

With Me.ListBox1
.ColumnCount = 2
for each cell in rng
.AddItem cell
.list(.listcount -1, 1) = cell.offset(0,1)
Next
End With
End Sub
 
Loomah,

Remove the call to visible cells

Private Sub UserForm_Initialize()
'(failed) attempts to take data from a filtered list
'and use it to populate a 2 column listbox
Dim myArray() As Variant

'attempt to write values into array
'fails as only first area is written into array
myArray = Worksheets(1).Range("A2:b" & _
Cells(65536, 1).End(xlUp).Row).Value

With Me.ListBox1
'make 2 columns in listbox
.ColumnCount = 2
'populate
.List() = myArray
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
That would kind of defeat the purpose of filtering the data unless only data
at the bottom is to be filtered out - but if that was the case, the original
would have worked.
 
Thanks guys
I haven't time to check this now but may be posting back asking what
"list(.listcount -1, 1) " means
Bob
Tom's already commented but I didn't have a problem populating the listbox
with a full range only with a filtered list

Anyway, once again, a BIG thankyou to both of you!
;-)
 
The way I read it was that he was being thwarted when the data was filtered.

Bob
 
It adds your second column to the second column of the list box (row by row
in conjunction with adding the items in the first column).

I didn't completely clean up your original code, so here is a tested
revision: (lines were too long to see the end).

Private Sub UserForm_Initialize()
Dim cell As Range, rng As Range

'attempt to write values into array
'fails as only first area is written into array

With Worksheets(1)
Set rng = .Range("A2:A" & _
.Cells(65536, 1).End(xlUp).Row) _
.SpecialCells(xlCellTypeVisible)
End With

With Me.ListBox1
.ColumnCount = 2
For Each cell In rng
.AddItem cell
.List(.ListCount - 1, 1) = cell.Offset(0, 1)
Next
End With
End Sub
 
It appears he wants only the filtered (visible) data - that was the way I
read it and his latest post appears to confirm.
 
Back
Top