Autofilter issue

[AndrewGKenneth]

Hi there,

I am having a problem with trying to get my spreadsheet to filter automatically. To explain my problem I have a spreadsheet ("Sheet2") and in column A is the value that will be filtered. In column A i want only the values that are "TRUE" to show. For example, if A1 is True the values in A2 and A3 will show, if A1 is not True A2 and A3 will be filtered.

The problem i am having is that when True values are updated in column A the filter is not automatically updating. The code

Private Sub Worksheet_Change(ByVal Target As Range)
Activesheet.Autofilter.Applyfilter
End Sub

Does not work because it only updates the cells that are hidden and does not automatically refresh the TRUE values that will be visible. Can anyone help please?

 

[dmt32]

Hi,
bit of a guess but assuming that you have already applied the filter for your range & just want to update it then try this update to your code & see if this does what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    If Me.AutoFilterMode Then Me.ShowAllData: Me.AutoFilter.ApplyFilter
End Sub

Note that the change event does not trigger if your values are being changed by formula.

Dave

 

[AndrewGKenneth]

Ah that will be why then the data being entered into Cell Range A is from a formula, so will not be affecting the change trigger. Is there anyway around this? Thanks Dave

 

[dmt32]

Ah that will be why then the data being entered into Cell Range A is from a formula, so will not be affecting the change trigger. Is there anyway around this? Thanks Dave

you can try using the Calculate Event however, this does not have target argument to determine which cell has change but there are ways to manage this.

Private Sub Worksheet_Calculate()
    With Me.Cells(1, 1)
        If .ID <> .Value Then
            .ID = .Value
            If Me.AutoFilterMode Then Me.ShowAllData: Me.AutoFilter.ApplyFilter
        End If
    End With
End Sub

Solution uses Range.ID property as a place holder to compare the cells value & if this changes, run your line code.

Hope Helpful

Dave

 

[AndrewGKenneth]

Thanks Dave, I will try this on Monday and let you know how it goes!