Repeat Cut and Paste

[Hector89]

Hello,

Im trying to learn VBA and Macros to write a code that will cut and paste specific parts of a row repetitively throughout the whole sheet and skipping a row. I have created this so far:

Range("C7 : H7") .Select
Selection. Cut
Range ("I6") .Select
ActivateSheet .Paste
Range("C9 : H9") .Select
Selection. Cut
Range ("I8") .Select
ActivateSheet .Paste
End Sub

What I need the code to do is to cut from C7 to H7 and then paste on I6. Then I need it to skip a row and do the same function, cut from C9 to H9 and paste on I8. I need this to happen on the whole sheet. I have read of something called loop but I dont know how to use it or if that would work.

Anyone's assistance is much appreciated.

 

[Yongle]

Welcome to MrExcel
Test this on a COPY of your worksheet

Sub CopyPaste()
    Dim r As Long
    For r = 7 To Range("C" & Rows.Count).End(xlUp).Row Step 2
        Range("C" & r).Resize(, 6).Cut Range("I" & r - 1)
    Next
End Sub

 

[Hector89]

You are a genius mate!!!

if I can bug you with something else. It seems the code keeps going and going and I need to hit esc for it to stop and then it freezes the excel for a while.

I’ve tried to add after “Next” the following:

Next Until ActiveCell.Value = “ “

but it doesn’t work. Do you know how I could make it stop once there is nothing else to cut?

 

[Yongle]

I am very puzzled by your comment
Did you amend the macro in any way ?
If you did, post the macro that you used

Otherwise tell me what the message box returns when you run this one liner against the sheet

MsgBox Range("C" & Rows.Count).End(xlUp).Row

Does it return the row number of the last cell containing a value in column C ?

 

[Hector89]

My bad Yongle. Ignore my previous comment. It’s working like a charm.