Refresh or reapply autofilter after updating cell

[3link]

I think what I want to accomplish here is very basic but I can't seem to find a solution in the public domain.

In my sheet, I have a cell (B10) with a drop down menu (basically data validation). When I select an option from that dropdown menu, the values in Column D (starting at D18 and ending at D42) can change from anywhere between zero and non-zero values (they are tied to an index/match function that uses B10 as the match). The end result I am looking for is this: whenever B10 updates, I want to apply a filter between D18 and D42 that filters out all zero values, so the only thing visible between that range are the nonzero values. It goes without saying that I'd like to make sure that the filter is reapplied only after the cells in D18 to D42 have updated based on my selection in B10.

Any help would be greatly appreciated.

 

[Sam_D_Ben]

Try this,

Private Sub Worksheet_Change(ByVal X As Range)
    Dim A As Worksheet, B As Range: Set A = Me: Set B = A.Range("D18:D42")
    If Not Intersect(X, A.Range("B10")) Is Nothing Then
        Application.ScreenUpdating = False: Application.EnableEvents = False
        Application.Calculate: Application.Wait (Now + TimeValue("00:00:01"))
        If A.AutoFilterMode Then A.AutoFilter.ShowAllData
        B.AutoFilter 1, "<>0"
        Application.EnableEvents = True: Application.ScreenUpdating = True
    End If
End Sub

 

[3link]

Try this,

Private Sub Worksheet_Change(ByVal X As Range)
    Dim A As Worksheet, B As Range: Set A = Me: Set B = A.Range("D18:D42")
    If Not Intersect(X, A.Range("B10")) Is Nothing Then
        Application.ScreenUpdating = False: Application.EnableEvents = False
        Application.Calculate: Application.Wait (Now + TimeValue("00:00:01"))
        If A.AutoFilterMode Then A.AutoFilter.ShowAllData
        B.AutoFilter 1, "<>0"
        Application.EnableEvents = True: Application.ScreenUpdating = True
    End If
End Sub

Works! Thank you!