Hiding or filtering ?

[Khalil Handal]

I have a sheet that represent records each record has a code such as:
K,K1,K2 1, 2, 3, ... 12, 12A, 12B
The sheet has about 800 lines of data records. The code is in colomn B .

For simplicity reason, Is it possible that If I type one of the codes in
cell C1 to see only the lines with that specific code.
If I type 6a in cell C1 then I would see only line with that code.

Is it better to use hiding line or filtering? This is because the data in
this sheet will be used in other sheets...
A VBA code that works automaticly is more preferable than cliking a button
(macro).
Any advice is appriciated. I am not an expert in VBA.

 

[Don Guillett]

You could develop a macro where you type in (better yet use a drop down)
linked to a worksheet_change event that either hides or filters.

 

[Khalil Handal]

Can you help with the code please, Filtering might work better???

 

[Gord Dibben]

Create a Data Validation>List dropdown in B1 with your codes as selections.

Then copy/paste this event code into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("B1")
If .Value <> "" Then
With Me.Range("C:C")
.AutoFilter Field:=1, Criteria1:=Me.Range("B1").Value
End With
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Khalil Handal]

Hi,
Thank you. It worked fine.

One more thing, if possible.
Can I have a Data Validation>List dropdown in another sheet at the same
workbook that shows only the filltered range defined by your code?

Thank again for the help.

 

[Gord Dibben]

See Ron de Bruin's site for copying autofilter results to another sheet.

http://www.rondebruin.nl/copy5.htm


Gord

 

[Khalil Handal]

Very Helpful,
thank you

See Ron de Bruin's site for copying autofilter results to another sheet.

http://www.rondebruin.nl/copy5.htm


Gord