Autofilter - Run Dynamically?

[DarrylBurge]

On the attached spreadsheet I have the following code:

Please Login or Register  to view this content.

my issues are:

The second Autofilter returns a Runtime 1004 - is this because I already have an autofilter in place? If so, how do I go about combining both of them?

And

Is there any way to dynamically run the autofilter? It is created so that only 1 blank row is visible, but I would like the next row to become visible once the bottom blank line has a date entered into Column A.

[davesexcel]

Shouldn't it be A5 not A9?

Please Login or Register  to view this content.

[DarrylBurge]

Hi Dave

Thanks for the reply.

Yes, you are right, it should have been A5.

Column A gets filtered when I have added your code, but Column I does not, and I get a 1004 error.

Please Login or Register  to view this content.

[davesexcel]

Ah yes, I had turned off the filter when I was testing it. Try this to make sure the filter is off before filtering again.

Please Login or Register  to view this content.

[davesexcel]

Here is an edited code to hide all the arrows:

Please Login or Register  to view this content.

[DarrylBurge]

That is absolutely cracking.

Is there any way to have then run dynamically/automatically? When I add a date into A23 I would like row 24 to appear - either as soon as I hit the return after adding the date, or when I next open the workbook allowing me to add another episode of illness

[Artik]

The attachment should solve your problems.
Most of the code that is currently in the Sheet1 module should be in the ThisWorkbook module, but will need to be customized for that module. Because it doesn't make sense to copy code to multiple sheets with the same structure and functionality. If you can't handle the migration to ThisWorkbook, that's a topic for a separate thread.

Artik

[DarrylBurge]

Hi Artik

Thank you. I am otherwise engaged today, but will look at what you have done and see if I can work it out when I get a chance.

[DarrylBurge]

Artik

Thank you so much. That works amazingly. Just need to work out now how to move it to ThisWorkbook. Wish me luck

Can I ask though, why is it not a good idea to have it on the Sheet1? is it because it is easier to make future changes? or will some of the code error if the worksheet is copied?

[Artik]

From your code, I inferred that there would be multiple sheets with the same structure and functionality, and therefore the same code. So it doesn't make sense to copy the code to each sheet. In such cases, you use workbook-level events (in the ThisWorkbook module). In your case, these will be 3 events: Workbook_SheetCalculate, ..._SheetChange, ..._SheetSelectionChange. If you don't foresee other sheets in the workbook that deviate from the structure of the sheet you presented, you can transfer the bodies of these procedures to the corresponding events in ThisWorkbook. Two auxiliary procedures: AutoFilter_Example1 and Sort_Tabs_Alphabetically, should be placed in a standard module, such as Module1. One event - ..._SheetCalculate - should be slightly modified so that it does not execute too often

Please Login or Register  to view this content.

Since a change in one sheet can cause another sheet to recalculate as well, I added a condition so that the _Calulate event executes only in the active sheet. Because, in this case, it makes no sense for other sheets. You replace the Me keyword with the Sh variable and add a condition for when the procedure body should execute.

After the code is moved, the code must be removed from the Sheet1 module, otherwise the events will execute twice. Once in the Sheet1 module and a second time in the Thisworkbook module.

Artik

[DarrylBurge]

Thanks for all your help on this Artik.

I have moved Sub Sort_Tabs_Alphabetically() to Modules/Module1, and Workbook_SheetCalculate to thisWorkbook as you suggested.

I am going to open a new post regarding moving the Worksheet_Change and Worksheet_SelectionChange to ThisWorkbook

regards

Darryl

[DarrylBurge]

After the code is moved, the code must be removed from the Sheet1 module, otherwise the events will execute twice. Once in the Sheet1 module and a second time in the Thisworkbook module.

Artik

Hi Artik

I am just about there. The code you gave me:

Please Login or Register  to view this content.

I can sort of see what it does, but I was wondering if there was any way of making it not so vociferous? By this I mean that I am unable to put ANYTHING into any cell in column A, I can't even Merge?Centre an entire row and add anything or it tells me to not change the date in Column A.

It's not the end of the world as I know I can Merge/Centre B-H and omit A, but it just makes it look a little untidy.

[DarrylBurge]

Removed the following code to allow the over-writing of Column A

Please Login or Register  to view this content.