If criteria met: delete cell and shift to the left but shift only over a certain range

Hi, not sure how clearly I can explain this but please look at the worksheet attached for a clearer explanation.

I have 9 Columns (C,D,E,F,G,H,I,J,K) with text in it. The number of rows varies so the code must look for the last non-empty row in column C.

I am looking for a code to look for a particular value in range D6:H? (e.g. "Name Whatever" located in a "Target" cell in the same worksheet).

If a cell in range D6:H? contains the target, the cell must be deleted and the cells must be shifted to the left but only over the range D:H.

For instance, suppose cell E10 contains the target, then the content of cell E10 would be replaced by that of cell F10, the content of cell F10 would be replaced by the content of cell G10, the content of cell G10 would be replaced by the content of cell H10 and the cell H10 would be blank. Importantly, the cells in C10 and I10:K10 would not be modified.

This means that if a match is found in cell H?, it simply gets replaced by an empty cell (no cell to the left to shift).

As I said, this is easier to understand by taking a look at the attached worksheet.

Many thanks for the help!

Example2.xls

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D2:H" & Range("C" & Rows.Count).End(3)(2).Row)) Is Nothing Then
    Select Case Target.Column
        Case Is = 4
            If Target.Text = "Name Whatever" Then
                Target.Clear
                Range(Target.Offset(, 1), Target.Offset(, 4)).Cut Target
            End If
        Case Is = 5
            If Target.Text = "Name Whatever" Then
                Target.Clear
                Range(Target.Offset(, 1), Target.Offset(, 3)).Cut Target
            End If
        Case Is = 6
            If Target.Text = "Name Whatever" Then
                Target.Clear
                Range(Target.Offset(, 1), Target.Offset(, 2)).Cut Target
            End If
        Case Is = 7
            If Target.Text = "Name Whatever" Then
                Target.Clear
                Target.Offset(, 1).Cut Target
            End If
        Case Is = 8
            If Target.Text = "Name Whatever" Then
                Target.Clear
            End If
    End Select
End If
        
        
                
End Sub

Maybe my choice of word was confusing. What I call "target" is just the cell where the name to look for "Name Whatever" is located in my worksheet. It's not the destination of anything. Makes any sense?

The code is a worksheet change event. Target in this case means if you select a cell in the designated range (ie. D2-H & lastrow) then that cell is the Target. If you put in "Name Whatever" in the Target cell then the shift you desired should take place. Sorry, if I misunderstood what you want to happen.

Thanks. So the Sub takes "target" as an argument? Not sure what this range refers to in your code?

Ok. I have managed to get it to work perfectly. Thank you so much.

You're welcome. Glad to help out, and thanks for the feedback.