VBA Code for shifting blocks of data...

[dominicwellsuk]

I'm having some problems using VBA to shift rows of data within a list, up or down in a sequence. I have the code below, which works if I select a single row, however, if I select more than one row, the offset value no longer applies. Is there a way of varying the offset value, depending on how many rows I select, so all of the selected rows shift down?

There's some extra small things in the code which aren't important so I hope you can pick out the important bits.

Thanks.

---------------

Sub Row_Down()
Dim Response As VbMsgBoxResult
Response = MsgBox("Do You Want to Move Selected Line Item in Assy Sequence?", vbQuestion + vbYesNo)
If Response = vbNo Then Exit Sub
Selection.EntireRow.Cut
Selection.Offset(2, 0).Insert Shift:=xlDown
Selection.Offset(1, 0).Select
RowVariable = ActiveCell.Row
Range("B5").Select
ActiveCell.FormulaR1C1 = "1"
Range("B6").Select
ActiveCell.FormulaR1C1 = "2"
Range("B5:B6").Select
Selection.AutoFill Destination:=Range("B5:B104")
Rows(RowVariable & ":" & RowVariable).Select

Range("C5:H5").Select
Selection.AutoFill Destination:=Range("C5:H104"), Type:=xlFillFormats
Range("C5:H104").Select
End Sub

 

[Ralajer]

Try this

    With Selection
        .EntireRow.Cut
        '// Selection.Rows.Count returns the number of rows in selection
        .Offset(.Rows.Count + 1, 0).Insert Shift:=xlDown
        .Offset(0, 0).Select
    End With

in place of this

    Selection.EntireRow.Cut
    Selection.Offset(2, 0).Insert Shift:=xlDown
    Selection.Offset(1, 0).Select