How do I get a filter to "UPDATE" the rows selected?

[Phillips]

Hey there!
I am using Excel 2002 and I have a speadsheet that has prices in it.

If I have the filter set to (the filter criteria is much more complex than
this though) prices for items that cost >$5.00.

Of I change an item to 4.59, it should not be seen. How can I get it to
re-update the rows shown, with out releaseing the filter and resetting it?
Is there something like refresh.filter or anything like that?

Thanks
Phil

 

[Don Guillett]

AFAIK you must release and reset but a macro with a worksheet_change event
could trigger this for you so that it would happen automatically.

 

[Don Guillett]

I forgot to mention that you should NOT post in all ngs as it wastes the
time. However, if you must, the way you did it is best.

 

[Debra Dalgleish]

YOu can use a worksheet_Change event, similar to the following. The code
is stored on the worksheet module of the sheet that contains the filter
(right-click the sheet tab, and choose View Code)

'==================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim filt As Filter
Dim Op As Long
Dim rng As Range
Dim ws As Worksheet
Set ws = ActiveSheet
If ws.FilterMode = False Then
Exit Sub
End If
Set rng = ws.AutoFilter.Range

If Intersect(Target.EntireColumn, rng) Is Nothing Then
Exit Sub
Else
i = Target.Column
If Not ws.AutoFilter.Filters(i).On Then
'do nothing
Else
Set filt = ws.AutoFilter.Filters(i)
On Error Resume Next
Op = filt.Operator
If Op = 0 Then
Range("A1").AutoFilter Field:=i, _
Criteria1:=filt.Criteria1
Else
Range("A1").AutoFilter Field:=i, _
Criteria1:=filt.Criteria1, Operator:=Op, _
Criteria2:=filt.Criteria2
End If

End If
End If

End Sub
'==================================