Move data rows

[adam]

Hi anyone,

I'm using the following code to spin through data rows on my worksheet. How could I make the code to move the data rows up and down with the help of the code. Any help on this would be kindly appreciated.

Code: Select all

Sub SpinButton1_SpinDown()
Dim vTemp As Variant
If ActiveCell.Row < ActiveSheet.Rows.Count Then
If Not IsEmpty(ActiveCell.Offset(2, 0)) Then
vTemp = ActiveCell.Offset(1, 0).Formula
ActiveCell.Offset(1, 0).Formula = ActiveCell.Formula
ActiveCell.Formula = vTemp
ActiveCell.Offset(1, 0).Select
End If
End If
End Sub

Sub SpinButton1_SpinUp()
Dim vTemp As Variant
If ActiveCell.Row > 1 Then
If Not IsEmpty(ActiveCell.Offset(-1, 0)) Then
vTemp = ActiveCell.Offset(-1, 0).Formula
ActiveCell.Offset(-1, 0).Formula = ActiveCell.Formula
ActiveCell.Formula = vTemp
ActiveCell.Offset(-1, 0).Select
End If
End If
End Sub

[HansV]

Code: Select all

Sub SpinButton1_SpinDown()
  ActiveCell.EntireRow.Cut
  ActiveCell.Offset(2, 0).EntireRow.Insert Shift:=xlDown
End Sub

Sub SpinButton1_SpinUp()
  ActiveCell.EntireRow.Cut
  ActiveCell.Offset(-1, 0).EntireRow.Insert Shift:=xlDown
End Sub

[adam]

Thanks for the help Hans. The current code moves the selected either up or down by one row. The intention is to move the selected row by either up and down within rows 18 and 37.

Any help on this would be kindly appreciated.

Thanks in advance.

[HansV]

You can build in a check to prevent moving a row out of this range yourself.