Filtering data in dynamic search box - Hiding Tables

[CorperateCiv]

Hello, I created a search box in excel to filter data in different tables that are listed in the spreadsheet.

This is the code I have so far:

Private Sub TextBox1_Change()

Dim tb As ListObject
Dim tb_End As Integer
Dim i As Integer


tb_End = ActiveSheet.ListObjects().Count

Application.ScreenUpdating = False

For i = 1 To tb_End

ActiveSheet.ListObjects(i).DataBodyRange.AutoFilter _
Field:=1, _
Criteria1:=[E2] & "*", _
Operator:=xlFilterValues

Next i

Application.ScreenUpdating = True

End Sub

This works to filter all the tables so that I can search for the value. However, I want to make it so that any table that does not contain the value appears hidden in the excel sheet to create a cleaner view. I was planning on using an IF statement and .hidden but I don't know how to refer to the Autofilter Value.

For example:

If the value is in the top tables:

If the value is in one of the bottom tables

The sheet also contains a lot of tables so any filtered value on the bottom, you need to scroll down to look. I would appreciate any suggestions or reccomendations that you have!

 

[saboh12617]

Hi, i propose you a small update to your sub :

Private Sub TextBox1_Change()

  Dim tb As ListObject
  Dim tb_End As Integer
  Dim i As Integer


  tb_End = ActiveSheet.ListObjects().Count

  Application.ScreenUpdating = False

  For i = 1 To tb_End
    ActiveSheet.ListObjects(i).HeaderRowRange.Rows.Hidden = False
    
    ActiveSheet.ListObjects(i).DataBodyRange.AutoFilter _
    Field:=1, _
    Criteria1:=[E2] & "*", _
    Operator:=xlFilterValues

    On Error GoTo hideTable
    Dim rowCount As Long
    rowCount = ActiveSheet.ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count
    
hideTable:
    If rowCount < 1 Then ActiveSheet.ListObjects(1).HeaderRowRange.Rows.Hidden = True
    On Error GoTo 0
  Next i

  Application.ScreenUpdating = True

End Sub

Basically it checks for each table if the filtered visible rows exist, if not (error raised), it hides the HeaderRow. To Show them on next function call i added .Hidden = False at the beginning of the loop. Hope it works!

 

[CorperateCiv]

Hi! This was a good idea for hiding the rows when they have no data in it during the search.

There is an error for rowCount = ActiveSheet.ListObjects(i).DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count

. Saying "no cells found" is there a better way to reference the rows?

 

[saboh12617]

Yes there is an error raised when the DataBodyRange is empty, ie. no match is found. But using Is Nothing wouldn't work so i ended up with this on error goto hideTable...

I just noticed on my code you have to update the indexes for ActiveSheet.ListObjects(1) to ActiveSheet.ListObjects(i) of course. My bad wrong copy paste, but it seems you corrected it.

Alternatively i propose you this option, but there is no better way to handle the error AFAIK.

Private Sub TextBox1_Change()

  Dim tb As ListObject
  Dim tb_End As Integer
  Dim i As Integer


  tb_End = ActiveSheet.ListObjects().Count

  Application.ScreenUpdating = False

  For i = 1 To tb_End
    ActiveSheet.ListObjects(i).HeaderRowRange.Rows.Hidden = False
    ActiveSheet.ListObjects(i).DataBodyRange.AutoFilter Field:=1
   
    ActiveSheet.ListObjects(i).DataBodyRange.AutoFilter _
    Field:=1, _
    Criteria1:=[E2] & "*", _
    Operator:=xlFilterValues

    On Error Resume Next
    Dim rowCount As Long
    rowCount = 0
    rowCount = ActiveSheet.ListObjects(i).DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count
    On Error GoTo 0
    If rowCount = 0 Then ActiveSheet.ListObjects(i).HeaderRowRange.Rows.Hidden = True
  Next i

  Application.ScreenUpdating = True

End Sub

 

[CorperateCiv]

Hello @saboh12617. I forgot to mention my thanks! the code ended up working and the project is complete.