Populate ListBox from filtered list

[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

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[Loomah]

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!
;-)

 

[Bob Phillips]

The way I read it was that he was being thwarted when the data was filtered.

Bob

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

It appears he wants only the filtered (visible) data - that was the way I
read it and his latest post appears to confirm.