Autofilter using the contents of a particular cell

[Guest]

Is it possible to autofilter a list using the contents of a particular cell?
I have a list of rows that all have a Code Number. There may be 0 to 55 rows
that correspond to the Code Number. I need a way that will automatically
just display the rows matching the Code Number. I have several users and
none of whom that will take the time to learn how to autofilter. I think I
need an autofilter to filter based on the contents of a particular cell. The
contents of cell C5 is a unique Code Number, say JB007. I need the
autofilter to list only the rows, from a list that is 750 rows, to display
only the records that match this unique code. Is this possible? If so
please let me know how? Thanks for any assistance you can provide!

 

[Bernie Deitrick]

Matthew,

Copy the code below, right-click the sheet tab, select "View Code", and past ethe code into the
window that appears.

When the value in cell C5 is changed, the list that starts in cell E8 will be filtered based on
column E for the code number entered into cell C5.

Note that Column E must be the left-most column (column # 1 in the list) or you will need to change
the Field:=1 line to reflect that.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$5" Then Exit Sub
Range("E8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C5").Value
End Sub

 

[Guest]

Bernie: Thanks for the help, but I could not get it to work!
I did the right click and paste the code into the view code as suggested. I
saved the file. I then changed the data and nothing happened. I’m using a
pick list so I’m sure the criteria matches. Any other suggestions? Nothing
is too remedial as I’m a novice! Below is the exact code I used! F3 has
the criteria! The data begins in cell A8. Do I have to name the list?

Mathew


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$3" Then Exit Sub
Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("C3").Value
End Sub

 

[Bernie Deitrick]

Mathew,

You need to use F3, not C3, as the criteria value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$3" Then Exit Sub
Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie: Again thanks for the help! Sorry to be so stupid, but I still could
not get it to work! I made the code change suggested, dumb mistake on my part
sorry. I saved the file. I then changed the data and nothing happened. Any
other suggestions? Nothing is too remedial as I’m a novice! Code is below.
When I view the code Microsoft Visual Basic under the tool bar I have
Worksheet and then over to the right it is Change is that correct?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$3" Then Exit Sub
Range("A8").CurrentRegion.AutoFilter Field:=1, Criteria1:=Range("F3").Value
End Sub

 

[Bernie Deitrick]

Mathew,

Post your address (or reply to me privately) and I will send you a working version...

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie: thanks for you help! My e-mail address is: Mathew_O'(e-mail address removed)

 

[Bernie Deitrick]

Sent.....

HTH,
Bernie
MS Excel MVP

Bernie: thanks for you help! My e-mail address is: Mathew_O'(e-mail address removed)

 

[Guest]

Bernie: Thank you very much! I'm not sure what you did! The code is the
same for both I must have missed something! It gives me a learning
opportunity to find my mistake! Again thanks!