Turn off auto filter in a table

Marcia · #1 10-16-2020, 07:26 PM

Hi, the below code filter data from the tRegistration sheet and show the selected data in a list box and Filtered Data sheet. If the data sheet is a range, the data sheet won't be affected by the code, but when I turned the data sheet into a table, the filter is activated.

Code:

Case "Business Address"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:X" & Sheets("tRegistration").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=TextBox25.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear

If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here2:
Else
ActiveSheet.Range("A2:X" & Sheets("tRegistration").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:X" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here2:
ActiveSheet.AutoFilterMode = False
Call Clear

How to make the code show the filtered records only in the list box and Filtered Data Sheet? The data in the tRegistration should remain intact.
I tried this but the problem is still there.

Code:

Sub FilterOff()
If ActiveSheet.ListObjects(1).ShowAutoFilter Then
    ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
    Else
    ActiveSheet.ListObjects(1).ShowAutoFilter = True
    End If
End Sub

Thank you.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to modify code for Auto filter based on two criteria ...	LearnerExcel	Excel Programming	13	02-14-2018 08:20 PM
How to turn auto-scrolling text to a video?	bigstarlet	Word	0	02-13-2018 10:45 AM
Auto Filter base on a value in another cell in same worksheet	z941714	Excel	1	08-14-2015 07:41 AM
How to turn off Auto Reply in Outlook 2010	mikepla	Outlook	3	11-15-2011 11:28 AM
Junk filter: auto delete blocked senders ONLY	andrew@murphymckay.com	Outlook	1	11-10-2011 02:30 PM