Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Filter Data and then remove filter if cell is empty VBA

0

Hello There, I used your Micro below to filter range according to cell value. however, when I delete the cell value, the filter stay on. I need to add to this Micro a code to remove filter if cell value is empty (A2 in this example). Thank you for your help

Private Sub Worksheet_Change(ByVal Target As Range)

' TeachExcel.com

Dim filterInput As Range

Dim filterRange As Range

Set filterInput = Range("A2")

Set filterRange = Range("A4:A16")

If Not Intersect(Target, filterInput) Is Nothing Then

    filterRange.AutoFilter Field:=1, Criteria1=filterInput, _

    VisibleDropDown:=True

End If

End Sub

excel
MS Office Version: 365
posted by: Turbo on Sun Sep 27, 2020 at 7:19 pm
Answer
Discuss

Discussion

Thanks for your reply, I actually still having the same problem. when A2 has no value, the filter still on and not shwoing anything.
I attached the file with your code so you can check. Thanks in advance for your help
Turbo Sep 27, '20 at 10:47 pm
Add to Discussion

Answers

0

When you remove the arrows the filter is also removed and you see the entire list again. I don't believe that is what you had in mind. However, I did modify your code to change the list when you enter a different criterium in A2.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 096

    Dim filterInput As Range
    Dim filterRange As Range

    Set filterInput = Range("A2")
    Set filterRange = Range("A4:A16")
    If Not Intersect(Target, filterInput) Is Nothing Then
        With filterRange
            If Not ActiveSheet.AutoFilterMode Then .AutoFilter
            If Len(Target.Value) Then
                .AutoFilter Field:=1, _
                            Criteria1:=filterInput, _
                            VisibleDropDown:=False
            Else
                .AutoFilter
            End If
        End With
    End If

End Sub

Setting the VisibleDropDown to True would leave you with the arrows to indicate that you are looking at the filtered list. Let me know if this doesn't work for you.

posted by: Variatus (rep: 4889) on Sun Sep 27, 2020 at 10:17 pm
Discuss

Discussion

I modified the code to include an extra condition: if A2 is blank no filter should be applied and, therefore, the entire list shown.
Variatus (rep: 4889) Sep 28, '20 at 7:59 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login