Filtering A List

how can i filter a list using combobox .... then then copy the filtered list to new range

attached is the sample...

Hmmm,i assume that you want an unique list of all the districts in
ComboBox1 so...
In this example i did put an CommandButton on sheet "MMSC".
(the button is not needed but only as an example).

Private Sub CommandButton1_Click()
Dim R As Range, UniqVal As Variant
Dim UniqList As New Collection

ComboBox1.Clear 'clear the combo otherwise we gonna add items each time we run the code

On Error Resume Next

For Each R In Sheets("Data").Range("C2", Sheets("Data").Range("C2").End(xlDown))
UniqList.Add R.Value, CStr(R.Value) 'create a collection of unique values
Next R

For Each UniqVal In UniqList
ComboBox1.AddItem UniqVal 'put each of the collection values in the combo list
Next UniqVal

End Sub

Don't know how you add the districts name in sheet "Data" but the best way is using validation so the districts names are always written the same.
(since you want to make an unique list of them)
Now you've got an unique list its easy to write them down on a sheet.
Could do it in the same loop in which you fill the combobox...

thanks a lot for the help ....
i put the code in "Private Sub Workbook_Open()" so that everytime i open it, it'll populate the combobox ...

but how can i copy the filtered school code, name, and competitors to MMSC sheet ...

need hlp pls ... i'm new on this

thanks a lot for the help ....
i put the code in "Private Sub Workbook_Open()" so that everytime i open it, it'll populate the combobox ...

but how can i copy the filtered school code, name, and competitors to MMSC sheet ...

need hlp pls ... i'm new on this
This should do what you want:


Private Sub ComboBox1_Change()

'Turn off screenupdating
Application.ScreenUpdating = False

Dim R As Range
Dim ListRow As Integer

ListRow = 9

'Remove old data from table
While Not IsEmpty(Cells(ListRow, 1))
Range(Cells(ListRow, 1), Cells(ListRow, 3)).ClearContents
ListRow = ListRow + 1
Wend

ListRow = 9

'Loop through data and copy to output table
For Each R In Sheets("Data").Range("C2", Sheets("Data").Range("C2").End(xlDown))
'Have we got a match to the ComboBox
If R.Value = ComboBox1.Value Then
'Put into column 1 the data from 2 cells to the left of the district,etc
Cells(ListRow, 1).Value = R.Offset(0, -2).Value
Cells(ListRow, 2).Value = R.Offset(0, -1).Value
Cells(ListRow, 3).Value = R.Offset(0, 1).Value
'Increase ListRow so we write into the next blank line
ListRow = ListRow + 1
End If
Next R

'Turn screenupdating back on
Application.ScreenUpdating = True

End Sub

well .... you are a genius ....

it solved my problem .... the boss will not be angry with me when he comes in :)

thanks a lot for the help

Some minor modifications on TinyJack's code :

Private Sub ComboBox1_Change()
Dim R As Range, ListRow As Integer

Application.ScreenUpdating = False

Range("A9:C25").ClearContents 'Empty the Table on sheet "MMSC"
'avoid using loops as much as possible an this is only 1 line of code
ListRow = 9

'Loop through data and copy to output table
For Each R In Sheets("Data").Range("C2", Sheets("Data").Range("C2").End(xlDown))
'Have we got a match to the ComboBox
If R.Value = ComboBox1.Value Then
'Put into column 1 the data from 2 cells to the left of the district,etc
Cells(ListRow, 1).Value = R.Offset(0, -2).Value
Cells(ListRow, 2).Value = R.Offset(0, -1).Value
Cells(ListRow, 3).Value = R.Offset(0, 1).Value
'Increase ListRow so we write into the next blank line
ListRow = ListRow + 1
End If
Next R

Application.ScreenUpdating = True

End Sub

good point italkid .... thanks ....

another one ... is there a way to show on the chart only the ones on the list/table?

e.g. if there are 5 schools only 5 bars in the chart and if there are 3 schools only 3 bars will be shown in the chart ...

Hmmm,took a look at your sheet and played around with the schools
(adding and deleting some),and the chart seems to work properly so...?
3 schools = 3 bars,4 schools gives 4 bars etc.
Don't see anything wrong.

it's working fine i know ... but what if i add new schools ...

e.g. 10 schools for every district ...

is it possible to autolink the chart to whatever number of schools there is on the list?

additional ....

is it possible not to plot the blank cells?

Ahhhh,shure...
Sorry i misunderstand your question this morning (for me).
For somebody awake it whas an obvious question but i at that time
came just back from my work (8:30 AM !!).
So i edited the Combobox_Change code and you can implement it as it is
in your project.

Private Sub ComboBox1_Change()
Dim Ch As Chart
Dim Vr As Range, Xr As Range
Dim R As Range, ListRow As Integer

Application.ScreenUpdating = False

Range("A9:C25").ClearContents 'Empty the Table on sheet "MMSC"

ListRow = 9

'Loop through data and copy to output table
For Each R In Sheets("Data").Range("C2", Sheets("Data").Range("C2").End(xlDown))
'Have we got a match to the ComboBox
If R.Value = ComboBox1.Value Then
'Put into column 1 the data from 2 cells to the left of the district,etc
Cells(ListRow, 1).Value = R.Offset(0, -2).Value
Cells(ListRow, 2).Value = R.Offset(0, -1).Value
Cells(ListRow, 3).Value = R.Offset(0, 1).Value
'Increase ListRow so we write into the next blank line
ListRow = ListRow + 1
End If
Next R

Set Xr = Range("A9", Range("A9").End(xlDown))
Set Vr = Range("C9", Range("C9").End(xlDown))
Set Ch = Sheets("MMSC").ChartObjects(1).Chart

With Ch
.SeriesCollection(1).XValues = Xr
.SeriesCollection(1).Values = Vr
End With

Application.ScreenUpdating = True

Set Xr = Nothing
Set Vr = Nothing
Set Ch = Nothing

End Sub

As long as you don't replace the table on sheet "MMSC" the code will work.

geez ... i just got back to work ...

from the looks of it ....

it's working perfectly now

thanks for all the help on my project .... GOD bless you!