Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-16-2020, 07:26 PM
Marcia's Avatar
Marcia Marcia is offline Turn off auto filter in a table Windows 7 32bit Turn off auto filter in a table Office 2013
Expert
Turn off auto filter in a table
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Turn off auto filter in a table

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.
Reply With Quote
  #2  
Old 10-16-2020, 09:39 PM
Purfleet Purfleet is offline Turn off auto filter in a table Windows 10 Turn off auto filter in a table Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Can you please attaach a worksheet so we can see what is happening?
Reply With Quote
  #3  
Old 10-16-2020, 10:05 PM
Marcia's Avatar
Marcia Marcia is offline Turn off auto filter in a table Windows 7 32bit Turn off auto filter in a table Office 2013
Expert
Turn off auto filter in a table
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Here it is Purfleet
Attached Files
File Type: xlsm Form MS Form.xlsm (355.5 KB, 8 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Turn off auto filter in a table 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
Turn off auto filter in a table Auto Filter base on a value in another cell in same worksheet z941714 Excel 1 08-14-2015 07:41 AM
Turn off auto filter in a table 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:47 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft