need help with filtering a list

[neowok]

I have several tables of data one above the other, each with the sam
columns. there are some blank cells. A copy of the workbook im usin
can be found at http://www.darkcity.nildram.co.uk/listtest.xls

what i need to do is filter the entire lot based on the 'location
column (which is always filled in each of tables I need to filter) an
list all the rows that 'contain' the selected location.

Autofilter almost does what i need. The problem is that in some of th
location cells, there is more than one location. For example at th
top of the "track related projects" table in the sample i provided
there is "High Barnet, Totteridge". autofilter picks it up as exactl
that, but i need it to pick it up as "high barnet" AND "totteridge" s
that if a user selects EITHER high barnet or totteridge from the list
it brings up all the rows from all the lists that contain those tex
strings in the location column. I have no idea about writing th
code/function that would give me a filtered list like autofilter doe
but list the rows properly. It is possible to select 'custom' whe
using autofilter on the location column, and then use "contains" an
type in the item you want, but then anyone that uses it is going t
have to do this ALL the time to guarantee the correct result i
produced, or risk having missing rows, so this isn’t good. So someho
producing the list without autofilter is the only way to eliminat
these problems I think. I also really need to use a combobox where
can resize the fonts in it so that users can read the text (because th
chart the box will be placed on is extremely large and must be zoome
out to view, and the autofilter text is not resizable and its so tin
you cant read it when zoomed out). If the filtering can be done b
somehow manipulating the autofilter then this will probably be easies
and the font size problem doesn’t really matter, but i dont think that
possible.

Because the items in the dropdown list (i.e. the station names) wil
never change, I have made a list of exactly what text needs to b
searched for in the location column when each item in the list i
selected, and so can populate the combobox with these items, this lis
is on “sheet1” of my example. What I cant do is when clicking one o
the items in the dropdown, make it search the location column an
produce the results. I can easily define the area that needs to b
searched by highlighting the area on the sheet that contains the table
to be searched and then naming this area searcharea or somethin
similar, then tell it to search the relevant column in this searchare
and return every row that contains the given text. Except I need th
function to do this and to know how I make it run this function when
user clicks one of the items in the list. Also the rows that ar
returned need to keep their current formatting because they are colou
coded so that users know what table they have come from (the othe
possibility is having the function produce the list with the relevan
titles for each section as well, ie. For rows that came from th
‘station related projects’ table, put them under a station relate
projects title in the newly filtered list, and the same for rows fro
the other sections so that people can see where each row came fro
rather than relying on the colour coding to work it out). Users o
this will only have read access, only me and 1 other person will b
able to edit any of the data.

Ideally after the above is working it would also be very useful to b
able to sort the remaining list by order of the start date column wit
earliest dates at the top, and blank dates at the bottom. I trie
using the autofilter and then data/sort option and it actuall
rearranged the data in the tables themselves rather than JUST sortin
the current list so it ended up moving things around in the differen
tables which is obviously no good, and I really need to automaticall
sorted rather then going through menus. This is obviously far les
important than getting the first filtering part to work though.

If anyone could have a look at the sample i liked to above and work out
how to do this it would be most appreciated as i have no idea and dont
know vb very well at all. Im guessing theres probably things that can
do exactly this already since I imagine it’s a fairly common problem,
its just the few specific things I need that stop this being as simple
as using autofilter.

Thanks

 

[Dick Kusleika]

neowok

Try this for starters: Add an entry to the top of the list on Sheet1 that
reads (All). Put a combobox from the Control Toolbox on your sheet. Set
the ListFillRows property equal to the list on Sheet1!A1:A51. resize the
combobox and its font so that you can see the entries. Click on the View
Code button and insert this code

Private Sub ComboBox1_Change()

If Me.ComboBox1.Value = "(All)" Then
Me.AutoFilterMode = False
Else
Me.Range("A34:I132").AutoFilter 4, _
"=*" & Me.ComboBox1.Value & "*", xlAnd, , False
End If

End Sub

When you select (All) from the combobox, the filter is removed. Any other
entry creates and autofilter and sets the custom filter to 'contains'
whatever is in the combobox.

That won't help you with the sorting end of it. I think for that you will
need to use an Advanced Filter and filter to a different location. I
thought you should try this first and see if it's even close to what you
want. If it is and you want to explore the Advanced Filter option, let me
know.

 

[neowok]

it sounds like it does most of what i want actually, ill give it a tr
and post back with the results later.

thanks.
D

 

[neowok]

ok i have set it up as described but selecting the items from th
combobox always provides a blank list, i.e. it seems to be returning
results during the filtering.

I am guessing the code that I just pasted in just needs a littl
debugging somewhere? I had a look but I cant work it out.

It does seem to be working exactly as desired except for returning
results every time

 

[Dick Kusleika]

neowok

Did you try all of the items on the list? Some of the items return zero for
me also. For instance, if you choose Edgeware, you'll get zero because you
have a spelling error (it's spelled Edgware on the main sheet). When I
choose Burnt Oak, I get 4 entries. When I choose Totteridge, I get 3
entries.

Try those two and tell me what you get.

 

[neowok]

LOL I just realised i was using the wrong list data, its working now.

one thing i noticed even on the working one is that the autofilte
dropdowns keep appearing on the columns even though it appears you
code should turn them off which is strange. any ideas why or how
might stop that?

Thank

 

[Dick Kusleika]

neowok

There's got to be a better way than this, but it's all I could come up with:

Private Sub ComboBox1_Change()

Dim i As Long

Application.ScreenUpdating = False

If Me.ComboBox1.Value = "(All)" Then
Me.AutoFilterMode = False
Else
Me.Range("A34:I132").AutoFilter 4, _
"=*" & Me.ComboBox1.Value & "*", xlAnd, , False

With Me.Range("A34:I132")
For i = 1 To .Columns.Count
If i <> 4 Then
.AutoFilter i, , , , False
End If
Next i
End With

End If

Application.ScreenUpdating = True

End Sub

 

[neowok]

ok thanks ill try that.

had an idea about the sorting, how about using autofilter once t
filter out the whole list instead of particular rows, then sort thi
filtered list somehow, then do autofilter again on the newly sorte
list?

problem with sorting the list is because the data needs to remain i
the same tables, doing a Data/Sort on it actually moves the data aroun
so that its all in the wrong tables when you take the filtering off.

ill leave the sorting if its not that easy as its not really hugel
important

 

[neowok]

got it all working nicely now. one last thing, if the filter produce
no results when a particular item is clicked from the combobox, i
there a way i can pop up some sort of msg box that says something lik
"there are no results" ?

thank

 

[Dick Kusleika]

neowok

Add this to the end of your sub

For Each cell In Me.Range("A35:I132").Columns(1).Cells
If Not cell.EntireRow.Hidden Then
VisCnt = VisCnt + 1
End If
Next cell

If VisCnt = 0 Then
MsgBox "no records"
End If

At the top, Dim cell as Range and VisCnt as Long.

 

[neowok]

yeah thats done it, i also turned off autofilter if theres no result s
it displays the whole list again without having to go back to sho
all.

thank