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]

My fault for not understanding the table object. Next attempt:

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

 

[scuddington]

This deserves a high five my friend. Works like a charm

 

[AFPathfinder]

Glad to hear it! If I would stop being so tunnel visioned, I coulda had it a few posts ago.

 

[scuddington]

I spent weeks trying to get that fixed lol. I thought I would ask you one other question before I start a new thread.

I am also trying to autosort with vba, I have found some code but nothing for what I need it to do.
-I want to auto sort all rows based on column A, It will either be a Y or N.
-Would like all the Y to the top of the page
-All the N to the bottom (my current table has 2000 lines so I want blanks in between separating the Y and N)

 

[AFPathfinder]

Of the values currently in those 2000 rows, are there blanks already in the filtered range or do you want to insert blank rows in between? It would be fairly easy to sort Z-A, but if existing blanks are in the set and need to be before the Ns, then that could be a bit more difficult. Maybe using a little trickery using specialcells xlCellTypeBlanks.

 

[scuddington]

I'll try my best to explain
I currently have an inventory list that will have stuff that is stock and stuff that is only purchased a few times in a year.
The sheet is set up like this
-Column A will be for (Stock Y/N)
-Column B SKU
-Column C is Description
-Column D is vendor

I have 1200 items entered and 1000 empty rows. I guess I could just start out with a code to simply autosort everything based on column A, making Y on top and N on the bottom. Do you know how I could accomplish this automatically every time an item is entered. My sheet will also be locked

 

[AFPathfinder]

Is it a userform that enters new items into the table? You can add a new table row programmatically instead of having a bunch of blank rows in the table.

 

[scuddington]

It seems like when i lock it down the table wont carry down when you add a new item, that is why I just added blank rows.

 

[AFPathfinder]

You may need to unprotect the sheet before it would work, but here is the syntax for adding a new row to the table:

ActiveSheet[COLOR=#000000][FONT=Menlo].ListObject([/FONT][/COLOR][COLOR=#000000][FONT=Menlo]1[/FONT][/COLOR][COLOR=#000000][FONT=Menlo]).ListRows.Add[/FONT][/COLOR]

Sorting is typically a pain to remember the syntax for, so I usually use the macro recorder to populate the syntax. Here is an excerpt I found on MSO Dev Center:

[COLOR=blue][FONT=Consolas]With[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] ListObject(1).Sort
[/FONT][/COLOR]         [COLOR=blue]With[/COLOR] .SortFields
            .Clear
            .Add Range([COLOR=#A31515]"SampleData[[#All], [Total]]"[/COLOR]), SortOn:=xlSortOnValues, [COLOR=blue]Order[/COLOR]:=xlDescending
        [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
        .Header = xlYes
        .Orientation = xlSortColumns
        .Apply 
[COLOR=blue][FONT=Consolas]End [/FONT][/COLOR][COLOR=blue][FONT=Consolas]With[/FONT][/COLOR]