AutoFilter using VBA (Hides every row that is blank)

scuddington

New Member
Joined
Dec 6, 2017
Messages
9
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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.
 
Upvote 0
Oops! I misread the code. Try this one:
Code:
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
 
Upvote 0
Oops! I misread the code. Try this one:
Code:
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
 
Upvote 0
How about:
Code:
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
 
Upvote 0
How about:
Code:
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.
 
Upvote 0
This one?
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top