CODEWITHSUNDEEP
excelvba
David Mendez
David Mendez

Reputation: 29

Filter cells using cell value as reference

i need to filter an excel worksheet using a cell value as reference.. Right now what i found was using vba, so this is my code

Private Sub Worksheet_Change(ByVal Target As Range)
   If (Intersect(Target, Range("f2")) Is Nothing) _
   Then
     Exit Sub
   End If

   Cells.AutoFilter Field:=2, Criteria1:=Range("f2").Value

End Sub

But everytime i change value of cell f2 it hides all cells and excel crashes, somehow... So what i need is: Use cell f2 as reference for criteria, to filter data in range a1:d100, so that when value in cell f2 changes, a1:d100 only shows lines in which the the criteria is true...

field 2 is the description of a product, like a computer, so every time i write in f2 the text "HP", it show only HP computers for example...

Upvotes: 0

Views: 3458

Answers (2)

David Mendez
David Mendez

Reputation: 29

Thanks to all, but i solved my problem, if anyone is interested here is my code

Private Sub Worksheet_Change(ByVal Target As Range)
   If (Intersect(Target, Range("f1")) Is Nothing) _
   Then
      Exit Sub
   End If
   Cells.AutoFilter Field:=2, Criteria1:="*" & Range("f1")
   Cells.AutoFilter Field:=2, Criteria1:="*" & Range("f1") & "*"


End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Tbl As Range
    Set Tbl = Range("A1:D100")
    If Intersect(Range("F2"), Target) Is Nothing Then Exit Sub
    Tbl.AutoFilter Field:=2, Criteria1:=Range("F2").Value
End Sub

NOTE: The filter may hide row #2. This may make it a little difficult to change cell F2.

Upvotes: 0

Related Questions