I created a workbook with two worksheets--sheet1 and sheet2.

Sheet1 held the data with the district names in column A (with A1 holding the
header).

Sheet2 held the list of unique district names in column A (with A1 holding the
same header that was used in Sheet1).

Then I put a listbox from the Forms toolbar on Sheet1 and a button from that
same toolbar right under the listbox.

I rightclicked on the listbox and chose Format Control
On the control tab, I made the input range Sheet2!a2:a21 (I only used 20
entries)

And I chose Multi in the selection type.

Then I assigned this macro to the button:

Option Explicit
Sub testme()
Dim myLB As ListBox
Dim iCtr As Long
Dim DestCell As Range
Dim myCriteria As Range
Dim HowMany As Long

Dim ActWks As Worksheet
Dim ListWks As Worksheet

Set ActWks = Worksheets("sheet1")
Set ListWks = Worksheets("sheet2")

With ListWks
.Range("c:c").Clear
.Range("c1").Value = .Range("A1").Value
Set DestCell = .Range("c2")
End With

With ActWks
Set myLB = .ListBoxes("list box 1")
With myLB
HowMany = 0
For iCtr = 1 To .ListCount
If .Selected(iCtr) Then
HowMany = HowMany + 1
DestCell.Value _
= "=" & Chr(34) & "=" & .List(iCtr) & Chr(34)
Set DestCell = DestCell.Offset(1, 0)
End If
Next iCtr
End With

If HowMany = 0 Then
MsgBox "Please select at least one item!"
Exit Sub
End If

Set myCriteria = ListWks.Range("c1").Resize(HowMany + 1)
.Range("a:a").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=myCriteria
End With

End Sub

This builds a list in on sheet2 based on the selected items in that listbox. C1
will hold the header. C2:C## will hold the selected items.

Then it essentiall runs data|filter|advanced filter with a criteria range of
what's in column C of sheet2.


Dwaine Horton wrote:

on a worksheet from the Forms toolbar.

"merjet" wrote:

Hold on. What kind of ListBox are you using? 1. On a UserForm. 2. On
a worksheet from the Forms toolbar. 3. On a worksheet from the Control
Toolbox toolbar.

Hth,
Merjet




--

Dave Peterson