General question about excel

[macarony]

If I have a list of names starting at A1 and then goes down the list next cell A2, lets say there are 8 names with John smith in A1 how can I write a formula that every time I run the routine it drops John smith down one place until his name reaches the end of the range of names, the next time I run the routine he goes back to A1 and the process repeats?
thanks

[macarony]

Sorry that should say all the names go down the list by one space every time the routine is ran

[Captain Sensible]

Not too clear what your after so might be best to give some real example but doesn't sound too hard to have some kind of rolling list if that's what you're after.

If we say the cells are A1:A8 you could simply copy the cell block down then copy the last cell back to A1. So something like

Code: Select all

Sub revolve()
Range("A2:A9").Value = Range("A1:A8").Value
Range("A1").Value = Range("A9").Value
Range("A9").Value = ""
End Sub


[macarony]

Thanks Captain that works