Automatically re-filter when I change a value in a cell

[Porcupine_]

Hello,

I have a column starting from cell D22, which is set on a filter to eliminate the value "N" and keep the value "Y". The value ("Y" or "N") is generated by a formula that looks at whether the corresponding value in column A matches a value selected from a dropdown list in cell A6.

The problem is, whenever I change the value in cell A6, the column D does not re-filter to show only "Y" values, and is left with "N" values.

I am looking for a way to re-apply filters on the sheet whenever I change a value in Cell A6.

I have tried using this VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.AutoFilter.ApplyFilter
End Sub

But it isn't working for me - it doesn't come up with an error, but it just doesn't re-filter.

Is anybody able to help with this?

Thank you,

Vicky

 

[Fluff]

Re: Automaticlaly re-filter when I change a value in a cell

Your code works for me. But try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address = "$A$6" Then ActiveSheet.AutoFilter.ApplyFilter
End Sub

 

[Porcupine_]

Re: Automaticlaly re-filter when I change a value in a cell

Thank you but unfortunately that's also not working.

It is in the 'Worksheet' and 'Change' category in the Sheet's code. I've also tried it in 'SelectionChange'.

I have it saved as an xlsm file. I can't work out why it's not working.

 

[Fluff]

Re: Automaticlaly re-filter when I change a value in a cell

Ok, try this

Private Sub Worksheet_Change(ByVal Target As Range)
    [COLOR=#0000ff]Stop[/COLOR]
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address = "$A$6" Then ActiveSheet.AutoFilter.ApplyFilter
End Sub

when you change the value in A6 the line stop should be highlighted. Is it?

 

[Porcupine_]

Re: Automaticlaly re-filter when I change a value in a cell

No that's also not doing anything. If I click 'reapply' in the sort and filter section, it reapplies the filter and shows the right cells, but it isn't doing it automatically.

Do I need to set the page up in any way - I have renamed the sheet - does that matter?

 

[Fluff]

Re: Automaticlaly re-filter when I change a value in a cell

Where is the code?
It should be in the sheet module. Right click on the tab in question & select View Code, a code window will open up. Is your code in there?

 

[Porcupine_]

Re: Automaticlaly re-filter when I change a value in a cell

Yes that's where it is

 

[Fluff]

Re: Automaticlaly re-filter when I change a value in a cell

Put this in a normal module, run it & then try changing the value in A6 & see if anything happens

Sub tst()
Application.EnableEvents = True
End Sub

 

[Porcupine_]

Re: Automaticlaly re-filter when I change a value in a cell

Still not working. I tried it in both a new module and the worksheet code

 

[Fluff]

Re: Automaticlaly re-filter when I change a value in a cell

Ok, couple of further questions
1) have you closed & reopened the file since saving as an xlsm?
2) is the drop down in A6 data validation, or something else?