Worksheet change event to move values up over non-contiguous ranges (remove spaces)

[L plates]

Hi All,

I have two non-contiguous ranges that I would like to have a worksheet change event move values up as they are entered into cells within the range. The net result should be to move values up to ensure that the cell below the last used cell is the next cell to be populated. I have attached an example of what I mean including before and after examples. I have tried several ways to find the last used cell and move values up based on that if the cell above is blank but have had no joy in getting it to work.

The code should concentrate on values in columns C & N and move adjacent values in columns D & O at the same time. This is to ensure any values pasted into cell pairs at the same time are moved at the same time. As soon as a value is moved, the row in columns C or N where the value has been relocated should be selected. I'm not sure if a worksheet change event applies to every key stroke or after enter has been pressed? If the code applied after the full value had been entered this would be great but understand if this is not possible. I do not need to sort the values at the same time, just move them up.

Alternatively, if this is not possible or is too difficult, I would be happy if the values could be reshuffled on the click of a command button. I have included this in the sample sheet just in case!

Could anyone please assist? The example is an exact copy of the format the full worksheet uses so was hoping that any VBA applied would be utilising this.

Many thanks,