Loop instruction for number of lines of data

**maacmaac** · 11-11-2008, 12:40 AM

I have the following code that needs to be executed only a certain number of times. The number of times it needs to be executed is dependent upon the number of rows of data. For example if I have 30,000 rows of data, I need the code executed 30,000 times. If I have 20,000 rows of data, I need the code executed 20,000 times. How do I set a loop up to count the total number of rows and then do a loop that number of times with the following code. (I know my code probably isn't the most effecient way to do this but it is the only way I could think of doing it with so many formulas and not freezing up Excel). Thanks

Please Login or Register  to view this content.

**Paul** · 11-11-2008, 12:50 AM

Hi maac,

I'm pretty sure we can make that process more efficient, even if a loop is still needed. Can you explain what you are doing in each iteration of the loop and why? It may be best if you can upload a copy of your workbook.

Just looking at the code, it appears you're copying the activecell + 7 cells to the right of it down one row, then going back to the original row and converting the formulas to static values. Yes?

**maacmaac** · 11-11-2008, 10:09 AM

Hi Paul,

You are correct with your comments. The code does work as follows:

1. copy the active cell + 7 cells to right "the formula"
2. move down one row and paste "the formula"
3. move up one row and convert to values
4. move down one row and repeat the process

I have uploaded the file per your request. What I am trying to do is to consolidate information. If you look at the Data tab, I have a list of Trade IDs in column A. Any time the Trade ID number is duplicated, I need to consolidate all the data for that Trade ID number. I am using a formula to consolidate the data (see formula tab). Once I have the data consolidated, I then copy and paste the values into the Desired Results tab and sort ascending by Column A.

I had a feeling that my code wasn't that effecient, but I couldn't think of any other way to do it. I originally had my formulas filled all the way down to row 40,000 but doing it that way caused the file to freeze up everytime.

Hope this makes sense.

BTW. I have a post under thread 661112 with the same file but was asking different question (code for formulas) https://www.excelforum.com/showthread.php?p=661112. Started this thread because I was going a different direction with the type of solution needed. Hope I didn't break any forum rules doing it this way. Wasn't sure if I needed to start a new thread if the question is different. Sorry if I didn't do this correct.

Thanks