Advanced Filter - row and column limits?

Question

I have been using a VBA routine in excel 2003 to filter fairly large ranges of data (45,000 rows and up to 35 cloumns) for several years.  I recently installed excel 2007 and I have run into issues with some of the workbooks that use this routine.  I tried to use the avanced filter manually on the workbooks in question and the advanced filter "locked up".  I am trying to determine why advanced filter works most of the time and fails in some cases.  By the way, I am running 2007 in compatibility mode.

Is there a limit on the rows and columns that can be used as the input range?

Could this issue be caused by running Excel 2007 in compatiblity mode?

Answer 1

A possible cause for this could be a compatibility issue for Office 2003 format used in Office 2007.

---

Ganesh Kumar N

Answer 2

I don't get an error message; the advanced filter window remains visible and the hour glass is displayed.  I pressed the escape button several time after several minutes and the regardless of how long I allowed advanced filter to run the filter results (after escape) were roughly 1/3 of the expected results.

The data range is 45000 rows and 30 columns and the criteria is one field = "=XX-YY" (XX-YY is a string).  This had worked flawlessly in 2003 and is working on most of the files created in 2003 and are now being run with 2007.  As I mentioned in my second post when I copied the data to a new excel 2007 workbook the advanced filter worked as it had in excel 2003.  By the way, the new workbook did not have the charts or the pivot tables that were on the original workbook.

I would like to understand what the root of the problem is because recreating all the workbooks (30+) will be time consuming and I would like to avoid that scenario.

Thanks,

Raul

Answer 3

Do you get any error message when the program is locked up?

How big is the data range you select to do an advanced filter?

---

Ganesh Kumar N

Answer 4

I copied the data (45000 rows and 30 columns) to a new workbook and ran my filtering routine.  It worked, so this eliminates the size of the input range (at least in this case) as the issue.  Does anyone have any suggestions or comments regarding why the advanced filter seems to get locked up?

Thanks,

Raul