Help needed with VBA code to replace values with names and delete others Values

Dear All

I really need help with this long winded process and am hoping someone can give me a code to be able to do what I need automatically.

I have attached a very simple workbook to explain what I need to do.
In column A I have cells that have RXXXX [Role] I need to replace that with the name above that cell which is in the format of AAA,BBB (1000).

Basically everytime I hit a cell in column A which is Rxxxx where xxxx is a number then replace it by the name above it which is in the format of AAA,BBBBB (xxxx) where xxxx is number. Then I need to copy hat name for all the blank cells below it if there is data for them in the corresponding Col B.

For example in the workbook enclosed I need to replace R14317 Sales Manager with Aaa,BBB (1000) and R14217 Sales Manager to be replace by CCC,DDD (1001) and then copy CCC,DDD (1001) all the way down from A11 to A17 and then R14317 to be replace by EEE,FFF (1002) and so on.
If possible I then need to delete cells in Col A that begin with BU_zzz (where zzz is a text such as access or fashion etc.), i.e. in Col A I want to delete A1 to A4 and then start the process o copying the criteria above.

This might be confusing but please let me know if I need to give further explanation. My actual work book has over 100 entries in the same format and currently its taking me over two hours to do this manually.

Any help is much appreciate it.

I think this should get you started. This replaces the RXXXX with the Aaa,BBB(1000) style and also gets rid of the BU_zzz. You'll have to explain a bit more where else you want to go from there. Hope this helps.

Please Login or Register  to view this content.

Thanks for this I'm going to give it a try a bit later. What I need to do is to repeat the same process until the end of Column A so I can carry out with my analysis. Thanks for your help, I presume this will repeat until it reaches end of column A

yeah it will go through all of column A. What are the rows you want to delete again? Right now the code just deletes the contents of the cell that has BU_zzz but does not delete the row. Do you want to delete the row?

I just need to delete the empty cells in column A so that in Col A I just have the names of the people.

Thank you so much for your help

I just tried this code and it work perfectly. Only thing is I also need to delete the cells that its not the name in Col A i.e. 2315 - Luxury Accessories
(A2) and 3119 - Div E
(A37) and so on.

Will this be possible. I understand if it will not be possible as I can do them manually but it will save so much time idf its done manually.

Your code will already save m considerable amount of time.

Thank you so much

Is there any pattern to what you need to delete? Does it start with four numbers each time? there any pattern to why it shows up, or is it arbitrary?

They all have a four digit number and - followed by site name I.e. 1234- Luxury and so on

Just to clarify my earlier post I have attcahed another spreadsheet which shows the result after running your code which worked absolutely great.

What I need to do not is to delete anything in cloumn A which has xxxx - <name>, they all in the same format where xxxx is a 4 digit number.
After that I need to look at Col B and if the cell is empty then delete the row, i.e. if B3 is empty then delete the whole row and shift cells up.

If this can be done then the whole process is complete as I have a code which then copes the name down for each person for their department.

If this is possible it will save me about 3 hours of work every week.

Thanks so much for this

rv02,

I think this should work. I added a final loop to go through and do the final deletion. There may be a more elegant solution but this will do the trick:

Please Login or Register  to view this content.

Oh and if this works, please mark solved and add some reputation point!

Thanks.

Thank you so much for your help. This has saved me so much time its unbelievable. Thanks again for your time.

Although the solution above works perfectly I just wonder whether I can do a modification to the code. When the code replaces anything with Rxxxx in Col A and copies the name , what I need is not to clear the col B because I have split the text in Col A which contains Rxxxx followed by the text Sales Manger so col B had Sales manager and col A will have Rxxxx. I need to keept the Sales manager in Col B and get rid of Rxxxx. When I do this with the code above it deletes the text Sales Manager in Col B . I've tried everything with no success. Any help is appreciate it. I hope I make sense.

I made this thread as unsolved as I need a quick modification to the solution which Romulo had kindly provided me with. Just to clarify my pervious post I want to split the Rxxxxx Sales Manager in the attached spreadsheet so Col B has text "Sales Manager" and Col would have the Rxxxx bit. I can do this by using the formula which splits the text but when I run the code above the Sales Manager text from Col B disappeared. Basically I want to leave Col B intact.

Thanks again in advance