autofilters and data corruption

[Anna_UK]

Repeated use of the filters in a spreadsheet is leading to the filter/data
freezing.

Can get around the filter problem by removing the autofilter and unhiding
the rows, but need to avoid the freezing.

Problem is specific to one tab of the spreadsheet only (where filtering is
done)
There's approx 40 columns and only 2311 rows of data.

Data is pasted in from Access and doesn't contain any vlookup/functions etc
etc

Root cause of problem may not be Excel, but solutions I've tried for my work
PC profile etc have not worked.

If anyone has found this before, any input would be useful - thank you

Many thanks

 

[CLR]

I've personally experienced some "freezing", actually just slow operation, on
some large databases with many formulas.........the Recalc takes lots of
time. Maybe that's what you're experiencing.

What version of Excel and Operating System are you using? Do you have lots
of RAM? Is your Hard Disk nearly full?

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

I'm not sure what this means, but if it means you can't scroll up or down, but
you can select cells using the mouse or arrowkeys on the keyboard, then maybe...

Maybe you froze the panes when the data was filtered (and had hidden rows).
Then unfiltered (data|filter|showall) and the freeze panes line was off the
screen.

For instance, you filter A1:X9999 (headers in row 1)
You filter so that the first visible detail row is row 1234.

You apply window|Freeze panes under the header row. Things look good, but you
actually froze rows 1-1233. Then if you show all the detail rows, it appears
that the screen is frozen--you can't see anything change when you scroll up or
down.

 

[Anna_UK]

It's freezing completely unfortunately, and further repeated filtering led
(in a previous version ) to complete file corruption. Creating a fresh
spreadsheet hasn't solved the problem.

All users experiencing the problem have approx 1GB of ram and are using
windows, and have lots of room on the hard drive. Excel verison is 2002 XP.

 

[HKaplan]

Did you try an MS Query to extract the data from Access, or did you
literally paste from open Access tables? There may be some hidden
formatting characters possibly. An MS Query would certainly bring the
data in clean.

 

[Anna_UK]

The latter .... I've literally copied and pasted in data from an open select
query in Access. I can use an MS query to keep it clean - can i just check
you mean the toolbar selection; Data - import external data - import data?

 

[Anna_UK]

Hi Dave, a valid suggestion but it's definitely not this.

What is happening is hard to explain - and may not even be Excel, it may be
a memory issue . Basically the functionality of the filter remains, eg non
blanks are not selected, but then the filter freezes so the user then can't
select all/the non blanks etc.

 

[HKaplan]

Yes but use a Data | Import External Data | New database query,
assuming you are in XL2003. It has an access default data source.
Pretty easy if you are a little familar with Access.

That may possibly bring in cleaner data.

 

[Anna_UK]

Will incorporate this into my process, can only help. Clean data is always
good! Thanks