Rotating List question excel

[MrFuzzyBootzz]

Im looking to make a rotating list of 9 names where the top name will go to the bottom of the list and the rest move up 1. Now I also need it where if the name is not available I click a button or selecet "No" and that name wont move on the list but the other names will. Is this possible?

 

[rpaulson]

try this on a copy of you file.

this code assumes for list of name starts in cell A1,the list of name can be as long as you like.

Sub Move_up()
n = Cells(Rows.Count, "A").End(xlUp).Row 'set last row with a name

Cells(n + 1, "A") = [A1] 'move fist name to the bottom

For r = 1 To n 'loop to mover every name up 1 row
    Cells(r, "A") = Cells(r + 1, "A")
Next r

Cells(n + 1, "A").ClearContents ' clear the last name on the list

End Sub

how would like to distinguish the names that do not move up the list?

hth,

Ross

 

[MrFuzzyBootzz]

I currently have a list selection for yes and No in the b column so if I have No seleceted it would be nice if that name wont move

 

[Micron]

An idea of what the sheet looks like would help. If there's data in the row below the 9th name, that code will over-write it, yes? If that is the case I think I'd use a collection and add an empty string where the name should not move.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rpaulson]

try this

names start in cell A1, List of names can be as long as you want.
if corrospoining column B has No then that name will not be shifted up.

Enjoy
-Ross

Sub Move_up()

x = Cells(Rows.Count, "A").End(xlUp).Row 'set last row with a name

'always move the first on to the end
Cells(x + 1, "A") = [A1]
[A1].ClearContents


r = 1
n = 1

888
If Cells(r + n, "B") = "No" Then
     n = n + 1
     GoTo 888
End If
     
Cells(r, "A") = Cells(r + n, "A")
Cells(r + n, "A").ClearContents
    
r = r + n: n = 1
If r > x Then Exit Sub 'no more names
GoTo 888

End Sub

 

[MrFuzzyBootzz]

Thanj you rpaulson that worked perfect.

 

[MrFuzzyBootzz]

Ive ran into an issue with your code. I have my list of names but if the first slot is selected as No the names erase when I click the buttom