Frustration -- Filtering a list with the click of a Button
 

Column one in my list consists of data that could either be one of fiv
values. The list is 300 rows long, so each row has either of the fiv
values in column one. My problem is that i wish to have five button
on the SHEET that would allow me to filter that list by the values i
column one. For example, button one will filter the list by one of th
five values. Button two would filter the list by another of the fiv
values. The ultimate result would be to have a button that i click an
the list is filtered automatically. I also want to have a button tha
resets the list so no filters are applied. I DO NOT want t
autofilter, and I've tried advanced filter with no luck. I've trie
searching the web for some VB code i thought i could plug in, but n
luck. I hope somebody out there could be of some help to me....
thanks

--
Message posted from http://www.ExcelForum.com

Frustration -- Filtering a list with the click of a Button
 

Why not use advanced filters controlled by worksheet controls?
Set up the criteria range as say "A5:A6"; Data Range to filter as "A9:A500"
Set up five commandbuttons on the worksheet, and assign some code as follows
to each, changing the value that gets populated into cell A6 (the filter
criteria).

' set this up for each control / value
Private Sub CommandButton1_Click()
Range("A6").Value = 1
Call AppFilter
End Sub

' set this once only
Private Sub AppFilter
ActiveSheet.ShowAllData
Range("A6").Value = 1
Range("A9:A500").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=
_
Range("A5:A6"), Unique:=False
End Sub


Cheers
Nigel

"Bhavin " wrote in message
...
Column one in my list consists of data that could either be one of five
values. The list is 300 rows long, so each row has either of the five
values in column one. My problem is that i wish to have five buttons
on the SHEET that would allow me to filter that list by the values in
column one. For example, button one will filter the list by one of the
five values. Button two would filter the list by another of the five
values. The ultimate result would be to have a button that i click and
the list is filtered automatically. I also want to have a button that
resets the list so no filters are applied. I DO NOT want to
autofilter, and I've tried advanced filter with no luck. I've tried
searching the web for some VB code i thought i could plug in, but no
luck. I hope somebody out there could be of some help to me.....
thanks.


---
Message posted from http://www.ExcelForum.com/