Macro that replaces repeating the same steps 12 rows apart

[anrichards22]

I have a macro that works fine I would just like to fine tune it so that it works more efficiently.
Currently it completes the same step 19 times (every 12th row), however, most of the time it only needs to complete 12 times but can vary from 6 to 33.

Any help in shortening the code to reflect the repeat steps and additional code to find the last row in the range and only complete until it reaches the last row would be much appreciated.



A snippet of the code:

Please Login or Register  to view this content.

[arlu1201]

Your code is not very clear. Maybe you can attach a sample file and explain what you want to do.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[anrichards22]

I have attached a sample file that does what I need to do.
As you can see from the sample, one part of the code repeats the same step over and over every 12th row.
As you can also see from the sample, sometimes the code runs on empty cells. I would like the code to run to the end of the data and stop. The end cell of the data varies from sheet to sheet.

Any help appreciated.
Thx

[arlu1201]

Can you upload your data before running the macro?

[Norie]

Perhaps this will give you a start or at least some ideas.

Please Login or Register  to view this content.

[anrichards22]

Can you upload your data before running the macro?

Attached file contains the data before I run the macro and the data after I run the macro.

[Norie]

The code I posted produces the results in the 'Data After Macro' worksheet.

[anrichards22]

The code I posted produces the results in the 'Data After Macro' worksheet.

Thanks Norie,

Your code works beautifully.

2 related issues:

1. I want to copy down a4 and b4 to the end of the range (the last used cell in column 'c' would do the trick I think?) instead of copying to the predetermined row229...I am not sure how I can do this.
2. I have an issue with the 'while' argument. Sometimes there are no values (blank cells) in the middle of the range so the code will see this as the end even though there are other values further down....is it possible to put in an argument that says wait until 'x' number of blank cells are found before ending?? I have tried changing the 'while rangesrc.cells(1,1) parameter without any luck!

[Norie]

1 I thought that might be a problem but it should be easy to fix.

2 That might/might not be a bit harder - I based the code I posted on your code not the data.

What would you want to happen if there are empty rows between data?

Should the data move up?

[anrichards22]

1. I just added that at the end because it approximates 19 repetitions of the other code! I have tried to code find last cell in column 'c' but it screwed up the rest of the data so I left it as a manual copy paste to a predetermined row.

2. I used your code and applied it to copy some other parts of my data which is not always there. If you can see in the finished data each row represents one string...sometimes there are blanks in the raw data but I still want to copy whatever data that belongs to the string, even if that means having some blank columns in the string.

Your help is much appreciated.

[Norie]

I won't be able to have another look until tomorrow I'm afraid.

[anrichards22]

You have been a great help!

When you find the time please take another look and post a reply.

Thanks again.