Advanced Filtering in Excel 1

[TheVillan]

Has anybody come across this problem mentioned below, and if so is there a fix? Any help would be greatly appreciated.

Quote from a teacher
This problem with advanced filters drove me nuts last Summer. I am using windows 98 and windows XP with Office 2000. Not all computers are affected but once the problem starts to occur with a particular machine, Excel will crash every time. The problem appears to be with an entry in the registry that gets corrupted and once corrupted Excel will crash from then onwards.
Before I let anyone do the test now I ensure that the machine they are using does not crash but am running out of usable computers. Reinstalling Excel has no effect which also suggests that the problem is to do with the registry.

I could not find any references to this problem in the Micorosft website.

I believe it is definately a bug and the only workaround is to educate learners to save their work after each question and particularly before the advanced filter is used and to check that the machines are working correctly in advance (this is our responsibility anyway).

I have a class about to start learning advanced filters so no doubt there will be major headaches next week!

End of quote

 

[Paul666]

Quite often with Excel, its registry entries become corrupt and "strange" things can start to happen with sorting/filtering etc. The solution in most cases is to delete the Excel registry key in HKCU\Software\Microsoft\Office\9.0 and also in the same place in HKLM. If you want to ensure Excel is working fine then delete these keys before the training course. The only other thing that would cause the filter to misbehave would be if you are using a corrupt Excel file to demonstrate with.

 

[TheVillan]

Thanks very much Paul. Will try this out and confirm back to you if it solves the issue

 

[DaleWatson123321]

TheVillan,

Aside from your apparant registry problem, I'm interested in learning more about the depth of your instruction on Advanced Filters.

The reason is because I've had significant experience using Excel's Advanced Filter function (and with Lotus 123 previously), and feel I can contribute information that can be useful in enhancing your course material.

I'd appreciate if you could email me so we can discuss the subject further.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[TheVillan]

Quote
If you want to ensure Excel is working fine then delete these keys before the training course.
End of quote

Do you delete the Excel folder and subfolders/ files etc.
or are you referring to a specific item

My assumption is that you delete the lot and when you start Excel up again it rebuilds what you have deleted. Is that correct?
Thanks

 

[Paul666]

Hi Villan,

Yes, delete the whole Excel folder and they will be recreated when Excel starts.

Paul.

 

[TheVillan]

Thanks Paul.
I can remember something like this back in the deep distant past, but I just wanted to be sure.

 

[TheVillan]

Paul
I have had a go and from what I can see, it sets everything back to virgin defaults. This could have consequences for anybody that has templates add ins such as toolpak etc. Am I correct there? Having said that if you are well and truly kna**ered what choice do you have.
Ok the other thing that I want to guard against, is what happens if you do this to a machine that is configured in a network, can it have any serious consequences?
I am just beeing very careful in case I get any comebacks. There are a number of teachers who have never used regedit before. I think you can see my concerns.
Anybody else that wants to stick their two penneth in is very welcome to.

 

[Paul666]

Villan, you are correct - the Excel settings will be set back to virgin defaults. Perfect for the start of a new training course! Just bear in mind that whenever you are working in the Registry it's best to backup whichever key you intend to delete......

Paul.

 

[TheVillan]

Thanks a lot Paul