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.

Sub custom_delete()

    Dim totalrange As Range
    Dim check As Range
    Dim LastRow As Long
'Find the last used row in a Column: column A in this example
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

Set totalrange = Range("A1:A" & LastRow)

For Each check In totalrange
    
    If InStr(check.Value, "R") = 1 Then 'check if it starts with R
        'take the value of the first cell above it which normall has the
        'Aaa.BBB(1000)-style code
        tempstring = check.End(xlUp).Value
        
        check.Value = tempstring
        check.End(xlUp).Clear

    ElseIf InStr(check.Value, "BU_") Then 'check if the value of the line starts with "BU-"
        
        check.Clear
    
    End If

Next check


    
End Sub

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 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

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

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:

Sub custom_delete()

    Dim totalrange As Range
    Dim check As Range
    Dim LastRow As Long
'Find the last used row in a Column: column A in this example
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

Set totalrange = Range("A1:A" & LastRow)

For Each check In totalrange
    
    If InStr(check.Value, "R") = 1 Then 'check if it starts with R
        'take the value of the first cell above it which normall has the
        'Aaa.BBB(1000)-style code
        tempstring = check.End(xlUp).Value
        
        check.Value = tempstring
        check.End(xlUp).Clear

    ElseIf InStr(check.Value, "BU_") Then 'check if the value of the line starts with "BU-"
        
        check.Clear
    
    End If

Next check

'loop through the rows and check for the two conditions (starts with four numbers and has an empty
'cell to its right in column B
For i = 1 To totalrange.Rows.Count
    
    If IsNumeric(Left(Cells(i, 1).Value, 4)) = True And IsEmpty(Cells(i, 1).Offset(, 1)) = True Then
    
        Rows(i).Delete
    
    End If
    
Next i

End Sub

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