Applying Filter but still reading all records

JONeill

Board Regular
Joined
Sep 2, 2018
Messages
58
So, I'm creating a search form that allows the user to search based on the fields in a data worksheet. There is a cover sheet to hide all the background stuff from the user. In the main form the data is read into a collection. Everytime I make a change like sorting, adding, updating etc., I erase the data and write the collection back to the worksheet. This is because, I'm using a rowsource for a listbox to get columns and even though I don't display the background steps, I still get a quick blink every time I make a change. This creates a few complexities when making changes to records when a filter is applied. To see what happens I've manually applied a filter and use the same code to read the records in. To my surprise, It reads all the records even though the filter is displaying about 2/3 of them. Even the listbox displays all of them. So, I tried applying the filter in the code and I get the same thing. Apparently, I'm not understanding something here. Maybe I'm just better off using Access.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Hi JONeill,

Have you tried Advanced Filter - Copy to another location? If running code, make sure ScreenUpdating is off.

Hope that helps,

Doug
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 

JONeill

Board Regular
Joined
Sep 2, 2018
Messages
58
I'll need a member email address to share it in DropBox. Hopefully, you won't laugh at my code! :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Ok, when you have the link, you only publish it here.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,064
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top