Autosort all data by date when value in a column is filled

[labfm5]

Newbie to vba..

I have a spreadsheet where I'm looking to autosort all columns from A2:AS5000, by date (column B2:B5000), when the value in column AS is non-blank.
AS currently contains a formula that says it will remain blank unless all columns from A:AM contains data.

So the flow I'm trying to achieve is:
- user enters data in all columns A:AM
- AS then changes from blank to data
- All data from A:AS autosorts by date. Date is entered in column B.

I've literally spent the last few hours on google and here reading old threads and trying to see if I could replicate some of the VBA codes I'm seeing. Unfortunately, i've been unsuccessful.

Any help in setting up the right VBA code would be greatly appreciated. I'm using Office 2016 for Mac.

[Richard Buttrey]

Hi,

You seem to be suggesting that the sort is conditional on AS. i.e. you don't want to sort any rows where AS is blank. Is that the case?
If so then two sort keys are needed.

The first sort key is AS which should be sorted Z-A and the second key is column B sorted ascending.

What's unclear is what's the trigger for this? Just because a cell formula reflects a different value won't of itself be able to trigger a sheet change macro. You could use an actual change to columns A-AM as a trigger, or just have a macro linked to a button which would resort when you clicked the button.

[labfm5]

Hi,

You seem to be suggesting that the sort is conditional on AS. i.e. you don't want to sort any rows where AS is blank. Is that the case?
If so then two sort keys are needed.

Thanks for your reply Richard.

If a cell in column AS is blank, then that corresponding row across columns A-AM will also be blank so nothing to sort really. AS is really a helper column that validates that the user entered info in all columns A-AM across a single row (and will be used in another formula).

What's unclear is what's the trigger for this? Just because a cell formula reflects a different value won't of itself be able to trigger a sheet change macro. You could use an actual change to columns A-AM as a trigger, or just have a macro linked to a button which would resort when you clicked the button.

If I understand you correctly, a macro can't be triggered by a cell changing value driven by a formula (from blank to not blank) but can be triggered by an actual entry of data into that cell?

My intent is that once the user fills all columns A-AM that then the data gets autosorted. So my thought was to put something that calculates if A-AM is filled, then that will cause the autosort to happen.

[labfm5]

Figured it out myself. For anyone interested, here's what I did....

- Recorded a macro of me filtering the date column in descending order.
- Go to VBA editor, go to the module created for the recorded macro, then copied to VBA code.
- Googled "vba code on cell value change"
- Found this and modified it:

Please Login or Register  to view this content.

Worked like a charm.