VBA Loop Script Writing
Budget: £20 – £250 GBP
I have attached a spreadsheet showing how the data will transform. These changes to the data are shown over a series of worksheets, but in the final version, this will all be happening on a single sheet in the final working version. This is just to help visualise the change.
So some clarifying points;
- There will always and only be 6 dogs in a race. So the loop can repeat easily.
- The amount of rows in a data set will vary however, so we need a loop that will keep going until it finds the last row of data
What do I want it to do?
It is a copy and paste job for each row that makes up a race (the EventID is a unique identifier for a race with the number repeating over 6 rows - 1 row for each dog)
We want to cut the data in columns I, J and K and paste it all onto one row - the first row of that race. It is a cut and paste job, where I need the range of columns (I,J & K) to remain together once on a single row.
They will always be copied onto the first row of that race, which will be every 7th row down starting from the 2nd race onwards.
I know how to delete the partial blank rows that will be left using VBA. The question, I guess is whether we should be deleting these rows as we go before moving onto the next race to cut and paste. This obviously impacts the position control / Offset for the loop to repeat.
From my limited knowledge of this it feels like we need a loop within a loop. The first loop would be to sort out the cut and paste action of the data for each race. The second loop would be the offset to move it down the data range to the next race (depending on when we would delete the rows - during or at the end once all the data has been moved).
Hoping that with the attached file showing you what is going on at different stages, it will make sense!
So some clarifying points;
- There will always and only be 6 dogs in a race. So the loop can repeat easily.
- The amount of rows in a data set will vary however, so we need a loop that will keep going until it finds the last row of data
What do I want it to do?
It is a copy and paste job for each row that makes up a race (the EventID is a unique identifier for a race with the number repeating over 6 rows - 1 row for each dog)
We want to cut the data in columns I, J and K and paste it all onto one row - the first row of that race. It is a cut and paste job, where I need the range of columns (I,J & K) to remain together once on a single row.
They will always be copied onto the first row of that race, which will be every 7th row down starting from the 2nd race onwards.
I know how to delete the partial blank rows that will be left using VBA. The question, I guess is whether we should be deleting these rows as we go before moving onto the next race to cut and paste. This obviously impacts the position control / Offset for the loop to repeat.
From my limited knowledge of this it feels like we need a loop within a loop. The first loop would be to sort out the cut and paste action of the data for each race. The second loop would be the offset to move it down the data range to the next race (depending on when we would delete the rows - during or at the end once all the data has been moved).
Hoping that with the attached file showing you what is going on at different stages, it will make sense!