AutoFilter using VBA (Hides every row that is blank)

[scuddington]

Wondering if I could get some help.

I currently have the following code in VBA, the code works well but it hides all rows with anything that is blank. I'm using this with a search box and the column has around 3000 lines. Thanks

Private Sub TextBox1_Change()
ActiveSheet.Unprotect "xxxxxxxx"
ActiveSheet.ListObjects("DESCRIPTION").Range.AutoFilter Field:=1, _
Criteria1:="=*" & TextBox1 & "*"
ActiveSheet.Protect "xxxxxxx", UserInterfaceOnly:=True
End Sub

 

[AFPathfinder]

What do you want it to do or not do?

 

[scuddington]

When i do a search the code filters down the criteria, but when i delete out my search bar it wants to keep all lines that are blank hidden. So i guess I want the code to show all rows no matter what after I clear the search box

 

[AFPathfinder]

It unprotects just to hide blank rows and then protects the sheet again. If you don't want to hide the blank rows, I'd say get rid of the whole subroutine.

 

[AFPathfinder]

Oops! I misread the code. Try this one:

Private Sub TextBox1_Change()
    If TextBox1.Value <> "" Then
        ActiveSheet.Unprotect "xxxxxxxx"
        ActiveSheet.ListObjects("DESCRIPTION").Range.AutoFilter Field:=1, _
        Criteria1:="=*" & TextBox1 & "*"
        ActiveSheet.Protect "xxxxxxx", UserInterfaceOnly:=True
    End If
End Sub

 

[scuddington]

Oops! I misread the code. Try this one:

Private Sub TextBox1_Change()
    If TextBox1.Value <> "" Then
        ActiveSheet.Unprotect "xxxxxxxx"
        ActiveSheet.ListObjects("DESCRIPTION").Range.AutoFilter Field:=1, _
        Criteria1:="=*" & TextBox1 & "*"
        ActiveSheet.Protect "xxxxxxx", UserInterfaceOnly:=True
    End If
End Sub

Looks like that code is hiding even more lines on me

 

[AFPathfinder]

How about:

Private Sub TextBox1_Change()
    If TextBox1.Value = "" Then
        ActiveSheet.ShowAllData
    Else
        ActiveSheet.Unprotect "xxxxxxxx"
        ActiveSheet.ListObjects("DESCRIPTION").Range.AutoFilter Field:=1, _
        Criteria1:="=*" & TextBox1 & "*"
        ActiveSheet.Protect "xxxxxxx", UserInterfaceOnly:=True
    End If
End Sub

 

[scuddington]

How about:

Private Sub TextBox1_Change()
    If TextBox1.Value = "" Then
        ActiveSheet.ShowAllData
    Else
        ActiveSheet.Unprotect "xxxxxxxx"
        ActiveSheet.ListObjects("DESCRIPTION").Range.AutoFilter Field:=1, _
        Criteria1:="=*" & TextBox1 & "*"
        ActiveSheet.Protect "xxxxxxx", UserInterfaceOnly:=True
    End If
End Sub

I love the dedication in helping me lol. Doesn't like the following "ActiveSheet.ShowAllData" when i clear the search box.

 

[AFPathfinder]

This one?

Private Sub TextBox1_Change()    If TextBox1.Value = "" And ActiveSheet.AutoFilterMode Then
        ActiveSheet.ShowAllData
    Else
        ActiveSheet.Unprotect "xxxxxxxx"
        ActiveSheet.ListObjects("DESCRIPTION").Range.AutoFilter Field:=1, _
        Criteria1:="=*" & TextBox1 & "*"
        ActiveSheet.Protect "xxxxxxx", UserInterfaceOnly:=True
    End If
End Sub

 

[scuddington]

Still hiding lines. I just don't understand it