Hiding or filtering ?

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
K

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.
 
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.
 
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
 
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.
 
Back
Top