CODEWITHSUNDEEP
excelvbarangemoveoffset
AugustinB
AugustinB

Reputation: 25

VBA move selected range up / down (with offset?)

I am building a tool where user-selected cell contents is moved around with arrow shapes.

The code below works great to move 1 or more group of adjacent cells down. However, reversing the code seems tricky (+1 in offset does not work :-?)

Any idea? Thank you, Augustin

Sub Move_Up()
    Selection.Cut
    Selection.Offset(-1, 0).Select
    Selection.Insert Shift:=xlDown
End Sub

Upvotes: 1

Views: 23772

Answers (2)

user3598756
user3598756

Reputation: 29421

supposing cells are to be moved around and overwritten ones are just shifted where moved ones once were, the code could be the following:

Sub MoveUp()
    Selection.Rows(Selection.Rows.count + 1).Insert Shift:=xlDown
    Selection.Rows(1).Offset(-1).Cut Selection.Rows(Selection.Rows.count + 1)
    Selection.Rows(1).Offset(-1).Delete Shift:=xlUp
    Selection.Offset(-1).Select
End Sub

Sub MoveDown()
    Selection.Rows(1).Insert Shift:=xlDown
    Selection.Rows(Selection.Rows.count).Offset(2).Cut Selection.Rows(1)
    Selection.Rows(Selection.Rows.count).Offset(2).Delete Shift:=xlUp
    Selection.Offset(1).Select
End Sub

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96773

If you want to move a Selected block of cells up by one row then:

Sub ShiftBlockUp()
    Dim r As Range
    Set r = Selection
    Intersect(r(1).EntireRow, r).Offset(-1, 0).Delete Shift:=xlUp
End Sub

If you want to move a Selected block of cells down by one row then:

Sub ShiftBlockDown()
    Dim r As Range
    Set r = Selection
    Intersect(r(1).EntireRow, r).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Upvotes: 0

Related Questions