Autofilter Question

[Qaspec]

Can I use VBA to identify the value in a cell and update the autofilter for a
specific column with that value?

When user types a name into A2 the the autofilter for column c updates to
filter for that name.

 

[Gord Dibben]

Sub Filter_Stuff()
Dim what As String
Dim collett As Integer
collett = 3 'could use an inputbox to enter column number
what = Range("A2").Value
With ActiveSheet.UsedRange
Selection.AutoFilter
Selection.AutoFilter Field:=collett, _
Criteria1:=what, Operator:=xlAnd
End With
End Sub

You could also call this from event code in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A2")
If .Value <> "" Then
Call Filter_Stuff
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("A2")) Is Nothing Then

On Error Resume Next
Me.Columns(3).AutoFilter
On Error GoTo 0

Me.Columns(3).AutoFilter field:=1, Criteria1:=Target.Value
End If

End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Qaspec]

Thank you. THat was a great help. I had to modify it just a little bit so i
could avoid hiding rows in the auto filter i did not want to hide.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("A2")) Is Nothing Then

On Error Resume Next
Me.Columns(10).AutoFilter
On Error GoTo 0

Worksheets("Sheet1").Range("A14:C318").AutoFilter field:=3,
Criteria1:=Target.Value

End If

End Sub