[SOLVED] Macro (advancedfilter) is also triggered when inserting rows

Macro (advancedfilter) is also triggered when inserting rows

I found this amazing VBA code that automatically filters when a specific cell range changes (e.g., 1,2,3,4). However, whenever I insert a row, it automatically triggers the macro and filters it because the new row has zero value. Unfortunately I have very limited knowledge and unsure how to modify the macro so that it doesn't automatically execute when I add a row. Any help would be greatly appreciated! See below on the code used:

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("e10:e80").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("f6:e7"), Unique:=False
End Sub

I tried adding the following code to the end, but it didn't seem to work?

application.enableevents = true

Solution

Try something like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Stop macro if more than once cell was changed
    If Target.Cells.Count > 1 Then Exit Sub

'Apply advanced filter if the criteria range was changed
    Dim rgCriteria As Range
    Set rgCriteria = Me.Range("E6:F7")
    If Not Intersect(Target, rgCriteria) Is Nothing Then
        Me.Range("E10:E80").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rgCriteria
    End If

End Sub

I also recommend looking into the Range.CurrentRegion method for identifying the entire data range when new records are added, as well as the entire criteria range if the number of criteria changes. For example: Range("E10").CurrentRegion and Range("E6").CurrentRegion. Note: with this method, the data range and criteria range must be separated by at least one blank row or column.