Reorder cells when dragging and replacing

[perathebird]

Hello excel community.

I want to make a system where I can see positions of cargo pallets in sliding storage.

Let's say that each cell(color) represents one type of cargo.

When Red pallet repositions to A5, I want to move all other painted cells automatically up by one row instead of just replacing it with Purple. Image example provided below.

Is this possible to do in Excel? If so, any guidance is very much welcomed.

[#2 · Accepted solution]

@perathebird 

Here is a suggested solution with VBA.

Sample file is included.

I would be happy to know if I could help.

NikolinoDE

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

Hey @NikolinoDE, thanks for the reply.

What I wanted to do is to replace the last cell with the first one and move all cells (including the last one) one row above. But not deleting any values.

[#4]

'm a little irritated when you switch the first to the last, how are the cells supposed to go up one?

[#5]

Oh, Sorry.

I figured that I need to replace A1 with A6 and then click a button.

Neat solution. Thanks!

[#6]

@perathebird 

Here again with an additional button for moving lines.

You can choose the order of the buttons as you wish :).

I also wish you a lot of fun with Excel.

Thank you

Nikolino

The more I know, the more I realize that I don't know anything.

(Albert Einstein freely based on Socrates)

[#7]

Thanks!

BTW, Is it possible to automatically delete 1st row when reordering?

When A1 is moved to A6 to automatically delete empty 1st row, without any button?

[#8]

@perathebird

Is that what you mean?

In the file the example with the button "Swap 2 lines with dell empty cell"

Hope I was able to satisfy you with it.

Thx

NikolinoDE

What speaks against doing something good when nobody is looking?