VBA: delete duplicates in a range except for the first one

[Nelson78]

Hello everybody.

I have a situation like this:

https://imgur.com/a/1MTsnEQ

I need, for the range B2:R2, to delete all the value "closed" except for the first.


My attempt is not concluded successfully (I can count the occurrences, but not delete).

Dim rng As Range

Dim q As Integer
Set rng = Worksheets("Foglio1").Range("B2:R2")
q = Application.WorksheetFunction.CountIf(rng, "*closed*")

 

[Nine Zero]

Try this out.... tested and works

Sub ByeClosed()
Dim r As Range
Dim counter As Integer
counter = 0
For Each r In Range("B2:R2")
        If r.Value = "Closed" Then
            counter = counter + 1
                If counter > 1 Then
                    r.Value = ""
                End If
        End If
Next
End Sub

 

[Nelson78]

Well, it works perfectly.

Now, I'm trying to shift to left the populated cells.
The problem is the following: on the right of column R, other columns are populated and these have to stay where they are (not shifted to left).

How can I manage this?

(this is the uncorrect code: it shifts to left, but also shifts columns on the right of column R).

Dim rng2 As Range
    Set rng2 = Range("B2:R2").SpecialCells(xlCellTypeBlanks)
        rng2.Rows.Delete Shift:=xlToLeft