Re-applying auto filter on select worksheets

OliviaT

New Member
Joined
May 28, 2015
Messages
17
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
Try...

Code:
[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

New Member
Joined
May 28, 2015
Messages
17
Try...

Code:
[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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
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?
 
Last edited:

OliviaT

New Member
Joined
May 28, 2015
Messages
17
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!!!
 

Forum statistics

Threads
1,082,300
Messages
5,364,381
Members
400,796
Latest member
vrcdesktop

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top