AutoFilter using VBA (Hides every row that is blank)
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: AutoFilter using VBA (Hides every row that is blank)

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default AutoFilter using VBA (Hides every row that is blank)

     
    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

  2. #2
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

    What do you want it to do or not do?

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

    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

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

    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.

  5. #5
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

    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

  6. #6
    New Member
    Join Date
    Dec 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

    Quote Originally Posted by AFPathfinder View Post
    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

  7. #7
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

    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

  8. #8
    New Member
    Join Date
    Dec 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

    Quote Originally Posted by AFPathfinder View Post
    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.

  9. #9
    Board Regular
    Join Date
    Apr 2012
    Location
    Texas, USA
    Posts
    515
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

    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

  10. #10
    New Member
    Join Date
    Dec 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AutoFilter using VBA (Hides every row that is blank)

      
    Still hiding lines. I just don't understand it

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com