?
Solved

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

Posted on 2009-02-23
9
Medium Priority
?
467 Views
Last Modified: 2013-11-25
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!
0
Comment
Question by:Aldo222
  • 4
  • 4
9 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23715941
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
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 23716055
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
 

Author Comment

by:Aldo222
ID: 23716075
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23716135
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
 

Author Comment

by:Aldo222
ID: 23716172
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
 

Author Comment

by:Aldo222
ID: 23716202
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
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 1000 total points
ID: 23716243
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
 

Author Closing Comment

by:Aldo222
ID: 31550331
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
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23716348
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question