Moving rows up and down with VBA

dannyok90

Board Regular
Joined
Aug 30, 2016
Messages
115
Hi all,

I have two sets of code (below) to move cells up and down using two little up and down buttons at the beginning of each row.


For context, the spreadsheet is for adding subtasks into a project plan, that may need to switch places in the progressive order hence the up and down arrow keys.


In the upper 4 cells there are titles and countas etc. that so would like the up arrow to not move a row above the 4th line if the button is accidentally clicked for example.


Again for the down arrow I would not like to not go past the bottom row because that goes into a different section of the plan, and its where new rows get inserted.

I imagine using a helper column or something but I'm not sure..

Any help? :)



Thanks,
Dan



Code:
Sub RowsUp()
     ' Mainlineup Macro
    Dim b As Object, cs As Integer
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        cs = .Row
    Rows(cs).Select
    Selection.Cut
    Selection.Offset(.Rows.Count - 2).Insert
    .Select
    End With
End Sub
Code:
Sub RowsDown()
     ' Mainlineup Macro
    Dim b As Object, cs As Integer
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        cs = .Row
    Rows(cs).Select
    Selection.Cut
    Selection.Offset(.Rows.Count + 1).Insert
    .Select
    End With
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
try
Code:
Sub RowsUp()
     ' Mainlineup Macro
    Dim b As Object, cs As Integer
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
      cs = .Row
      If cs <= 5 Then Exit Sub
      Rows(cs).Select
      Selection.Cut
      Selection.Offset(.Rows.Count - 2).Insert
      .Select
    End With
End Sub
And much the same for the down button
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top