Prioritize List on a Worksheet

In a previous Prioritize List on a Worksheet, I showed how to use a spin button to move values up or down. This code does the same thing, but moves the whole row rather than just one value. I tried to incorporate Eric’s comment, but it wasn’t any move efficient because I was either cutting the active row or the destination row.

Also, I lost the spin button probably because I had “Move and Resize with Cells” set. Rather than change that, I just forced the width of the spin button when I made it visible.

Private Sub SpinButton1_SpinDown()

Dim rDest As Range

‘Make sure not on the last row
If ActiveCell.Row < ActiveSheet.Rows.Count Then

‘set the destination cell
Set rDest = ActiveCell.Offset(1, 0)

‘don’t move off the list
If Not IsEmpty(rDest.Value) Then

‘cut the below cell and insert into activecell
rDest.EntireRow.Cut
ActiveCell.EntireRow.Insert xlShiftDown
End If
End If

End Sub

Private Sub SpinButton1_SpinUp()

Dim rDest As Range

If ActiveCell.Row > 1 Then
Set rDest = ActiveCell.Offset(-1, 0)
If Not IsEmpty(rDest.Value) Then

‘cut the activerow and insert above
ActiveCell.EntireRow.Cut
rDest.EntireRow.Insert xlShiftDown
End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 And Not IsEmpty(Target) Then
With Me.SpinButton1
.Visible = True
.Top = Target.Top + (Target.Height / 2) – (.Height / 2)
.Left = Target.Left + Target.Width + 10
.Width = 15
End With
Else
Me.SpinButton1.Visible = False
End If

End Sub