VBA to automatically reapply filter

ChrisTag1

New Member
Joined
Jan 13, 2020
Messages
9
Office Version
2016
Platform
Windows
I have a table that pulls data from another range based on a dropdown from another worksheet. I have tried VBA's to clear the blank rows based on a cell value and can't seem to get them to work. (I think it's because of the formula in the cells - {=IFERROR(INDEX(J$20:J$117,SMALL(IF($I$20:$I$117=DWOR!$G$71,IF($I$20:$I$117<>"",ROW(J$20:J$117)-ROW(J$20)+1)),ROWS(J$20:$J20))),"")} )
So instead of doing that, I thought I would try just filtering the blanks out, but I need it to automatically reapply the filter when the table data changes because this worksheet is going to be hidden eventually. I have tried a few different codes but none seem to work. I have never really used VBA as I only started using and learning excel in the last couple months.
1.) Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub


2.) Sub ReapplyFilter()
ActiveSheet.AutoFilter.ApplyFilter
End Sub

3.) Private Sub Worksheet_Change(ByVal Target As Range)
If Me.FilterMode = True Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

With ActiveWorkbook
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
.CustomViews("Mine").Show
.CustomViews("Mine").Delete
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If

End Sub


This last code, I don't understand any of it, so I can't even edit it to correspond with my own worksheet, if I even have to.
What I am doing is right clicking on my worksheet name, selecting view code, pasting the code in the screen, saving and then going back to excel. Maybe I am putting the codes in wrong, I have no idea.
Does anyone have an idea why these aren't working? My worksheet name is "Downtime Summary Primary HIDE", which I put into the first code. The second code seems pretty straightforward, no editing needed that I can see, yet still doesn't work.
Please help the not so smart newbie.


 

Attachments

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,467
Office Version
365, 2010
Platform
Windows, Mobile
If the data is changing by formula it needs to be in either a Worksheet_Calculate or a Workbook_SheetCalculate event, it won't trigger by a Worksheet_Change event.
 

Forum statistics

Threads
1,081,635
Messages
5,360,147
Members
400,574
Latest member
Mysterr42

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top