Excell how to make a cell rotate names when taken off top

[Guest]

In a seniority worksheet columm how can I set it up so that I can make a
macro that will move a name from the top of a column to the bottom sort of
like a rotate?

 

[Ken Johnson]

Hi Vicky,
I'm not sure if this is what you are after.
With the following macro, if you select the name of the person at the
top of the list, then run the macro, that selected name is sent to the
bottom and every other name moves up one row to fill the gap.

Public Sub top_to_bottom()
Dim rngRest As Range
Dim vaTemp As Variant
Dim iNumUsedRows As Long
Dim iDemotedRow As Long
iDemotedRow = ActiveCell.Row

iNumUsedRows = Range("A:A").Rows.Count - _
Range(Cells(Range("A:A").Rows.Count, ActiveCell.Column), _
Cells(Range("A:A").Rows.Count, ActiveCell.Column). _
End(xlUp)).Rows.Count

vaTemp = Cells(iDemotedRow, ActiveCell.Column).Value

Set rngRest = Range(Cells(iDemotedRow + 1, ActiveCell.Column), _
Cells(iNumUsedRows + 1, ActiveCell.Column))

rngRest.Copy Cells(iDemotedRow, ActiveCell.Column)

Cells(iNumUsedRows + 1, ActiveCell.Column).Value = vaTemp
End Sub

Ken Johnson