In VBA, how do I LOOP the following code UNITL the last row of data in a selected range? Thanks!

Hi: In VBA, I need to LOOP the following code UNITL the last row of data in a selected range.

    Range(ActiveCell.Offset(0, 51), ActiveCell.Offset(0, 0)).Select
    Selection.Copy
    ActiveCell.Offset(4, 0).Select
    ActiveSheet.Paste

Can someone insert the Loop Unitl code I would need for the above?  I've been trying to write it myself and getting stuck.  Thanks!
Aldo222Asked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
You meant this....

Dim i As Long
i = 1      ' row number you want to define to start from...
Do Until i > Cells(65536, "c").End(xlUp).Row
Range("D" & i & ":BC" & i).Copy Range("D" & i + 4)
i = i + 4
Loop

Open in new window

0
 
Saurabh Singh TeotiaCommented:
Aldo,
Can you give a clarity about what you are trying to do..as it becomes easy to write a loop statement for you post that...
Saurabh...
0
 
Ardhendu SarangiSr. Project ManagerCommented:
Try this code.. this should work for you..

- Ardhendu
Dim i As Integer, lstrow As Integer
lstrow = Cells(65536, ActiveCell.Column).End(xlUp).Row
Do Until ActiveCell.Row > lstrow
    Range(ActiveCell.Offset(0, 51), ActiveCell.Offset(0, 0)).Select
    Selection.Copy
    ActiveCell.Offset(4, 0).Select
    ActiveSheet.Paste
Loop

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Aldo222Author Commented:
Sorry. New at posting questions to the site...

The code I have listed successfully copies and pastes a variable row of data 4 rows below the range being copied.  I now need to continue to Loop this (copy the range then paste 4 rows below) unitl the very last data row in a data set.  The data set will change so I'll need to set up a code to look for the last data row and then in the loop until function reference it. I just can't figure out how to write the loop until and where to insert the set last data row appropriately.  

Does this help? Thanks!
0
 
Saurabh Singh TeotiaCommented:
Yes it does...but im just bit lost because if i study the code of Ardhendu it will paste the same set of row again and again...so your row would remain the same but it gets pasted automatically x amount of times leaving 4 rows...
and again selection method of coding is not a good way to do coding..so what i want from you is which column you want to check for last row...?? which row you are begining..?? and do you want to copy the same row or data..?? or you want to get that changed as well...???
0
 
Aldo222Author Commented:
Thanks, Pari.  Need to make the 1st row = to something other than the ActiveCell column because the active cell column does not have data in each line (only every 4 rows). In my data file, only Column C will have Data in all rows to be able to do an "End.Up"

How do I edit the code you provided to define the 1st row on Column C?
0
 
Aldo222Author Commented:
OK...D6:BC6 is what I want to copy every 4 rows until the last data row.  But each cell from D:BC is a formula not a value.
0
 
Aldo222Author Commented:
Thanks. This definitely got the job done.  The one thing I'm confused about going forward though is that I don't see a Paste function anyway, just a copy.  How did the loop know to paste it?
0
 
Saurabh Singh TeotiaCommented:
Well you see the continuation of copy line...which is basically a paste function only...telling excel where to paste the values...that is in vb you can write where to do paste all in the same line after giving a space..vb automatically does that.....
Saurabh...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.