Re-applying auto filter on select worksheets

[OliviaT]

Hi,

I have the code below to help me re-apply auto filter on selected sheets. It take multiple data entries to get the filter to auto apply. Can someone help me clean this up? Thanks!




Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L6:L24,E27:F1475,H27:H1475")) Is Nothing Then

With Worksheets("DS (4)")
.AutoFilter.ApplyFilter


End With

With Worksheets("TTM DS (4)")
.AutoFilter.ApplyFilter


End With

With Worksheets("DS (C)")
.AutoFilter.ApplyFilter


End With

With Worksheets("TTM DS (C)")
.AutoFilter.ApplyFilter


End With



End If

End Sub

 

[Domenic]

Try...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] vSheetNames [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] vSheetName [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Target, Range("L6:L24,E27:F1475,H27:H1475")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        vSheetNames = Array("DS (4)", "TTM DS (4)", "DS (C)", "TTM DS (C)")
        [color=darkblue]For[/color] [color=darkblue]Each[/color] vSheetName [color=darkblue]In[/color] vSheetNames
            Worksheets(vSheetName).AutoFilter.ApplyFilter
        [color=darkblue]Next[/color] vSheetName
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

Hope this helps!

 

[OliviaT]

Try...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] vSheetNames [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vSheetName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Target, Range("L6:L24,E27:F1475,H27:H1475")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        vSheetNames = Array("DS (4)", "TTM DS (4)", "DS (C)", "TTM DS (C)")
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] vSheetName [COLOR=darkblue]In[/COLOR] vSheetNames
            Worksheets(vSheetName).AutoFilter.ApplyFilter
        [COLOR=darkblue]Next[/COLOR] vSheetName
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

Hi Domenic,

Thanks for the response! However, it is still doing the same thing. I have to do a second data entry in one of my cells in the range for it to apply properly. Is there a way to make the auto filter run twice? Thanks again for your help!

 

[Domenic]

Actually, I misunderstood. I thought you wanted to re-write your code so that it would be more efficient. However, I've now tested it, and it seems to work fine. When I manually enter a value in one of the target cells, the autofilters are automatically re-applied. I didn't have to re-enter the data a second time. I've tested it in Excel 2016. Which version of Excel are you using?

 

[OliviaT]

Hi Domenic,

I am using Excel 2010. I found my issue. It's due to my formula which I don't think I can get around. My auto filter is set up to hide all blank cells. I have formulas in several cells where it returns a blank cell if certain cells value is zero. It appears the VBA code run before the formula returns the blank value and thus exclude these cells from hiding. I have to enter a second data entry in my target cells to re-apply the auto filter to hide my newly blank cells. Hope that makes sense. It's okay though, I have gotten around it by applying the same VBA code under another sheet that the user has to enter information. It runs the filter again so it worked out. I appreciate you cleaning up the code. I was asking for that as well. Thanks for all your help!!!