make filter automatically re-apply when cell values change

Hello,

I have a sheet which is full of formulas linked to another sheet.
So far, the first 4 rows have numbers in them. The remaining 96 rows have DIV/0! error.
So what ive done is ive put a filter on to not show the DIV/0! error rows, ONLY the numbers.
However, as i change data on my other sheet, the DIV/0! errors on this sheet become numbers.
But because the filter is already applied, these numbers do not show up. I have to re-apply the filter every time.

My questions is: Is there a way for excel to automatically know that a cell has changed from DIV/0! to a number and so therefore displays it in the filter?

Thank you.

am i right to put this under the VBA/Macros section?

any help please...

thanks JBeaucaire but i have no idea how to do that.

do you know?

Originally Posted by JBeaucaire

Right-click on the sheet tab and select VIEW CODE, then paste in this event macro:

Please Login or Register  to view this content.

"90" isn't the only numerical value that is going to be in that column.
the filter removes the rows with DIV/0!, not keeps only "90".

works a treat JBeaucaire. thank you.

one problem though. since inserting this code onto just 1 sheet, the excel workbook is SOOOOOO slow and unresponsive. i have a very high spec laptop which never runs slow on any program.
is this what macros do? is there anything i can do to make it run like before.

thanks

its still a little slow but you have certainly improved it.
thanks JBeaucaire - nothing short of a genius.

any other ideas to improve it a little more...?

dont do what? i dont understand.

the performance was fine. it's not as responsive as a blank workbook but its still fast and good to work with.
the code decreases performance substantially.

regarding your ws_activate event code...if i create charts on another sheet based on the sheet with the code in it, will the charts be updated from the autofiltered cells?

the ws_activate works perfectly.
doesnt look like the performance has been compromised.

thank you so much JBeaucaire for your time and effort.

one last thing...what extension is best to save the workbook in with macros?

Originally Posted by tallag

ive run into a problem
if i insert the ws_activate code AND protect the sheet, i get a "Run-time error '1004: Method 'Autofilter' of object 'Range' failed"
im given 2 options, END and DEBUG. neither fix the issue.

is it not possible to run the automatic filter code you gave me and protect cells at the same time?
im running excel 2011 on mac


by the way, the debugger highlights the following line from the code as the problem: Me.Range("G4:G124").AutoFilter 1, "<>#DIV/0!"

worked it out myself.
when protecting the sheet, make sure the filter box is checked

actually i was wrong. it appears that on mac the above is true (ticking the filter box when protecting the sheet is fine).
however, on windows i get the following error message:

Run time error '1004':
You cannot use this command on a protected sheet.........

this occurs even when Use AutoFilter box is checked when protecting the sheet.
It gives me three options: Debug; End; or Help. None help me.

Please help. thanks

i thought forums prefer not to start a whole new thread when it is very closely related to one youve already posted.
my bad. apologies.

i dont understand what you mean about "teach". do i need to add something to the macro? if so, what is it exactly?
cant understand why it works fine as it is on a mac but not on windows