lost_in_macros
New Member
- Joined
- Apr 13, 2015
- Messages
- 7
Hi awesome Mr. Excel community!
I am currently trying to create a rather complicated macro, and as you might have guessed by my user name, I am a bit lost right now.
This is how my data is currently shaped: I have one column which contains the data of owners (turquoise background, bgcolor=#CCFFFF or color=34 ) and tasks (white background). The number of owners, and the number of tasks per owner varies.
<tbody>
</tbody>
This is how I want my data to look like:
<tbody>
</tbody>
What I want my Macro to do:
Cut all turquoise cells in column B and paste them into column A (two columns to the left). Then, starting from the first filled cell in column A, I want my macro to copy this cell into the empty cells in the rows below until it reaches the next filled cell. Then, I want it to go down one cell, copy this new cell and paste it into all the empty cells below until it gets to the next filled cell. I want this to happen until it reaches the end of the data (so some sort of if...then copy..until..then go down one..then loop construction). In the end, I would like every row with an empty cell in B to be deleted.
I know this is quite a complicated request, so all suggestions are welcome! Please reach out to me if you have any additional questions, and have a great day!
I am currently trying to create a rather complicated macro, and as you might have guessed by my user name, I am a bit lost right now.
This is how my data is currently shaped: I have one column which contains the data of owners (turquoise background, bgcolor=#CCFFFF or color=34 ) and tasks (white background). The number of owners, and the number of tasks per owner varies.
A | B | |
1 | Owner | Task |
2 | Anna (turquoise background) | |
4 | Task 1 (white background) | |
5 | Task 2 (white background) | |
6 | Lisa (turquoise background) | |
7 | Task 3 (white background) | |
8 | Task 4 (white background) |
<tbody>
</tbody>
This is how I want my data to look like:
A | C | |
1 | Owner | Task |
2 | Anna | Task 1 |
3 | Anna | Task 2 |
4 | Lisa | Task 3 |
5 | Lisa | Task 4 |
<tbody>
</tbody>
What I want my Macro to do:
Cut all turquoise cells in column B and paste them into column A (two columns to the left). Then, starting from the first filled cell in column A, I want my macro to copy this cell into the empty cells in the rows below until it reaches the next filled cell. Then, I want it to go down one cell, copy this new cell and paste it into all the empty cells below until it gets to the next filled cell. I want this to happen until it reaches the end of the data (so some sort of if...then copy..until..then go down one..then loop construction). In the end, I would like every row with an empty cell in B to be deleted.
I know this is quite a complicated request, so all suggestions are welcome! Please reach out to me if you have any additional questions, and have a great day!