Move row range down in vba

Question

I am trying to select the first 7 rows of my table (the exact number may vary) and move 32 rows down (the number of rows to move down may also vary). Can someone help me with the code? I tried:

Worksheets("Report").Cells(x1, 5).EntireRow.Offset(32, 0).Select 

I also tried

 for i = 1 to 7 set x1 = worksheets("Report").Cells(i, 5) Rows(x1).EntireRow.Offset(32, 0).Select 

Will not work. Thanks in advance for your help!

Answer 1

This does what you ask, and if there are any lines after 32 shifts:

 Sub MoveRowsDown() Dim NumRows As Long Dim TargetRow As Long Dim ws As Excel.Worksheet NumRows = 7 'change as necessary TargetRow = 33 'change as necessary Set ws = ActiveSheet ' change as necessary ws.Range("A1").Resize(NumRows).EntireRow.Cut ws.Range("A" & TargetRow + NumRows).EntireRow.Insert shift:=xlDown End Sub 

EDIT: Here's a version that just cuts and pastes, without any fancy inserts:

 Sub MoveRowsDown() Dim NumRows As Long Dim TargetRow As Long Dim ws As Excel.Worksheet NumRows = 7 'change as necessary TargetRow = 33 'change as necessary Set ws = ActiveSheet ' change as necessary ws.Range("A1").Resize(NumRows).EntireRow.Cut Destination:=ws.Range("A" & TargetRow) End Sub 

Answer 2

try it

 Sub marine() ActiveSheet.Rows("32:38").Value = ActiveSheet.Rows("1:7").Value ActiveSheet.Rows("1:7").Clear End Sub 

replace activesheet with your sheet name. Assets are not the best

Answer 3

Just found this:

 range("A1:C6").Cut range("A10") 

Sweet!

I will also try:

 rows("1:7").cut rows("32")