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

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,

Hi,

Is it allowed to get rid of columns D,E & F so that the item & location are in columns C&D much like columns N&O are next to each other. In fact make the left hand table of cells the same 5 column range as the N:R columns.

A sorting solution would be simpler but that assumes the Items are numerics. Is that the case or is this just a simple example of your actual data.

Hi Richard, thanks for your input. One of my criteria is that the sheet must be utilised as it is. I thought of the sorting solution too, but I do not want to change the order a user inputs their data.

Hi Leo, thank you for your great solution. This has made my life a lot easier and I'm very grateful!

Many thanks,