The answer was already given to you a few posts ago.
You could have figured out the minor changes to the code yourself, they weren't that difficult.
This works fine for me.
Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long, dic As Object, k If Target.Column <> 8 Then Exit Sub If dic Is Nothing Then Set dic = CreateObject("scripting.dictionary") dic.comparemode = 1 End If dic.RemoveAll If Not Intersect(Me.UsedRange, Me.Range("H6:H1201")) Is Nothing Then k = Intersect(Me.UsedRange, Me.Range("H6:H1201")).Value2 If IsArray(k) Then For i = 1 To UBound(k, 1) If Len(k(i, 1)) Then dic.Item(k(i, 1)) = Empty Next If dic.Count Then With Sheets("MS") .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)).Clear .Cells(2, 1).Resize(dic.Count) = Application.Transpose(dic.keys) End With End If ElseIf Len(k) Then With Sheets("MS") .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)).Clear .Cells(2, 1) = k End With End If End If End Sub




Reply With Quote
Bookmarks