Trouble unfiltering rows

Insert a helper column and enter consecutive numbers. Include this column when you filter/sort. When you want to return to the original order, sort all the data on the helper column.

200K rows of data is a lot of data when you consider that you have 10 columns. Your Profile says that you are using Excel 2003 but that can't be correct as that version can only handle 65,536 rows. So, what version are you using? Please update your Profile with the correct Excel version that you are using. In addition, what version of Windows are you using and please give a description of your computer's specifications.

Freezing can have several causes some of which are, not enough free space on the working hard drive, not enough ram which causes much data swapping back and forth between the hard drive and memory and a computer that is just not up to the task hardware-wise.

Have you tried using Advanced Filter, copy to another location? That way you don't have to reverse the filter, just delete the results.

You can set up different filter ranges with different criteria in them in different places on the same worksheet or on a different worksheet. You have your filter setup on one worksheet and your partner on another.

A lack of computer resources can ruin ones' day for sure.

If you use the Advanced filter and copy to another location, then the original list is untouched and doesn't require "resetting". The data extracted to the other location, is not interfering with the original and the copy can be deleted when you are finished with it.

Here is an example of using the Advanced Filter twice to get two different results and the original data is untouched. There is no need to "unfilter" just delete the results when finished. You can leave the criteria ranges alone so that they can be used again.

Can you upload your workbook? Using the Advanced filter, you can filter on a lot of different items at the same time. If you can upload a sample of your workbook, give an example of what you are wanting to filter on and what you expect the result to be.

I ask this because your description doesn't give any indication of what your data looks like or what exactly you are trying to use as a filter.

After seeing what you want, the easiest way to get the results is to use the standard filter with the buttons.
First, select the Locations that you are interested in:
Location.JPG

Second, select the colours that you are interested in:
Colours.JPG

Result:

Result.JPG

When working with your data, I found that entering all the criteria for the Advanced Filter resulted in a very confusing set of criteria that I wouldn't wish on anyone.

You say that Excel "stops responding". This message will appear when Excel is still working and hasn't finished. It isn't the same thing as being "crashed" but you really can't tell the difference.

200k Rows is going to take a long time to process.

I would be tempted to make a temporary page to filter with. Copy the data to the temporary worksheet, filter the data and when finished, delete the temporary worksheet. It might work. I haven't a workbook with sufficient data to test that idea.

I have been experimenting with the advanced filter. You may want to visit this site to get some valuable information on the Advanced Filter http://www.contextures.com/xladvfilter01.html

The filter that I tried was rather strange in that the areas and colours were repeated down the criteria ranges. Here is the result with the filter criteria on the left and the result of the filter to the right.

	F	G	H	I	J	K	L
5	Location	Colours
6	area2	green		Event	Location	Colours	Agent
7	area2	white		B	Area2	White	Wilson
8	area2	blue		A	Area8	Green	Evans
9	area2	beige		B	Area6	White	Smith
10	area4	green		D	Area8	Blue	Andrews
11	area4	white		B	Area6	Beige	Evans
12	area4	blue		C	Area2	Green	Tomms
13	area4	beige		A	Area4	Beige	Wilson
14	area6	green
15	area6	white
16	area6	blue
17	area6	beige
18	area8	green
19	area8	white
20	area8	blue
21	area8	beige

The last method that I posted was what worked after many tries using different methods in the criteria area. I was always getting records that didn't belong and that isn't acceptable.

The criteria as entered in message #15 F5:G21 is the way to go. I would use a separate worksheet for the advanced filter and when done just delete the worksheet. This shouldn't cause any problems for Excel.

The Contextures site has a section for having the filtered data to appear on a separate worksheet which would make it easy to get rid of in your case.

If you follow these directions you should have no problems. Just remember that the Criteria Range MUST be on the same worksheet as the source data.

------
This is the text quoted from Contextures
If the database is on one sheet, you can extract data to a different sheet, by using an Advanced Filter. In this example, the data is on Sheet1, and will be copied to Sheet2.
1.Go to Sheet2
2.Select a cell in an unused part of the sheet (cell C4 in this example).
3. On the Excel Ribbon's Data tab, click Advanced
4. Choose Copy to another location.
5. Click in the List Range box
6. Select Sheet1, and select the database.
7. (optional) Click in the Criteria range box.
8. Select the criteria range
9. Click in the Copy to box.
10. Select the cell on Sheet2 in which you want the results to start, or select the headings that you have typed on Sheet2.
11. (optional) Check the box for Unique Values Only
12. Click OK
-------

I'm glad that we were able to find something that you can make use of. Thank you for the feedback.