How to bind filtered data in list box?

[Vinod]

Hi All,

I have developed a from in excel 2003 contains year dorpdown and listbox.
The list box source mapped/binded to a ragne and 'Year' dropdown has unique
years from the same range of listbox source.

My requirement is, if user selects year through 'Year' dropdown then the
list box has to refresh with selected year's data only. I tried to filter the
source data with selected 'Year' value but the listbox has been displaying
with old records i.e., still I see whole data includes hidden rows after
applying filter on source data.

I should not see the hidden rows in list box.

Is there any best method than implented or how to hide list box with hidden
rows from source range?

Please share your knowledge or thourghts in overcomeingt the issue which
will be appreciated.

Advanced Thanks,
~Vins.

 

[Dave Peterson]

You'll have to drop the rowsource for the year listbox.

Then you could loop through the filtered range and check to see if the row is
hidden. If it is visible then include it in the code to add the item to the
listbox -- or you could just look through the visible rows in that filtered
range...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim VisRng As Range
Dim myRng As Range
Dim myCell as range

Set wks = ActiveSheet

me.listbox1.clear 'remove any existing list.

With wks
'just a single column. I used column A.
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))

'remove any existing filter
.AutoFilterMode = False
myRng.AutoFilter Field:=1, Criteria1:="whateveryouneeded"

With .AutoFilter.Range.Columns(1)
If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then
MsgBox "only header visible"
Else
'avoid the header
Set VisRng = .Resize(.Cells.Count - 1).Offset(1, 0)
for each mycell in visrng.cells
me.listbox1.additem mycell.value
next mycell
End If
End With
.AutoFilterMode = False 'remove the filter
End With
End Sub

Untested, uncompiled. Watch for typos!

===============
Or you could drop the autofilter and just loop through the cells. If they match
a certain criteria, then use .additem to add it to the listbox.