make filter automatically re-apply when cell values change

[tlacloche]

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.

[tlacloche]

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

any help please...

[JBeaucaire]

Yes, you can create a Worksheet_Calculate() event macro to reapply your filter on that sheet anytime a calculated cell on that sheet updates.

[tlacloche]

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

do you know?

[JBeaucaire]

sure, turn on the macro recorder and let it record yuo applying a filter to your sheet (make sure I can see the sheet name) and post the resulting code here. We'll convert that to a Worksheet_Calculate() macro that happens automatically.

[tlacloche]

sure, turn on the macro recorder and let it record yuo applying a filter to your sheet (make sure I can see the sheet name) and post the resulting code here. We'll convert that to a Worksheet_Calculate() macro that happens automatically.

im very new to excel (well this advanced aspect of it)
i did as you asked and i got this. (the sheet name is "2. Name" by the way)

Please Login or Register  to view this content.

[tlacloche]

In fact maybe this one is better. this coloumn is better to apply the filter.

Please Login or Register  to view this content.

[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.

[tlacloche]

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".

[JBeaucaire]

1) please stop quoting my posts into yours, I already read what I wrote.

2) The macro I suggested was based on the recorded macro you said to use. Perhaps this is what you want:

Please Login or Register  to view this content.

[tlacloche]

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

[JBeaucaire]

Autofilters force a recalculation, so maybe you need to turn that off, stab in the dark here:

Please Login or Register  to view this content.

[tlacloche]

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...?

[JBeaucaire]

Yeah, don't do this.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[tlacloche]

dont do what? i dont understand.

[JBeaucaire]

Don't use the worksheet_calculate event to apply an autofilter. How was the performance of your sheet prior to this addition? You have to make the choice of efficiency vs convenience. If the automatic filter is more important than the resulting sheet performance loss, then keep it. Else take it back out.

Another idea would be to use the ws_activate event, so this only triggers when the sheet is brought up onscreen, not all the time.

Please Login or Register  to view this content.

[tlacloche]

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?

[JBeaucaire]

Sound like something you can test.

You can create "ws_activate" macros on the chart sheets that also force that sheet autofilter to update. The key I'm saying is to stop the filter from triggering all the time like it is in the "ws_calculate" macro.

[tlacloche]

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?

[JBeaucaire]

SAve as *.xlsm.

[tlacloche]

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!"

[tlacloche]

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

[tlacloche]

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

[JBeaucaire]

1) You would have probably gotten an immediate answer from our 100s of contributors if you'd simply posted this new problem as a new question. No one is reading this particular thread except us. Don't limit your new problems to old helpers.


2) You'll need to teach your macro to UNPROTECT the sheet, "do it's stuff", and PROTECT the sheet again. Millions of examples of this, or record the code yourself to see the syntax.

[tlacloche]

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