Get subsequently order by vba

[gandreso]

Hi,
I got a table that looks like in the following file.
I need too get all the names in a subsequently order. So in this case i want all the names under to get up.
Notice that I will have data to the left and right of this table so it will not be possible to just delete the rows with no value.
Example.xlsx
Example.xlsx

[gandreso]

Example.xlsx This is the correct file.

[nilem]

something like this

Please Login or Register  to view this content.

[gandreso]

Yeah like it does makes them subsequent order but I think i forgot to mention that I cant change the position of the names so sorting is not possible

[nilem]

maybe so

Please Login or Register  to view this content.

[gandreso]

Its better but I would like to have that the last four yellow rows always is on that exact row. Because right now they are also going up when the data is deleted.

[nilem]

ok, #3

Please Login or Register  to view this content.

[gandreso]

Thanks! You are the best!

[gandreso]

Your formula works great but can you explain in more simplier words what the formula is doing step by step